11 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Trapping fields of inconsistent lengths

    Carrie McCall

      I'm new to Monarch, and I'm trying to put our daily wires (we are a credit union) into a database using Monarch to separate the fields because there are inconsistent field lengths.  Each record has some manadatory fields which are at the beginning of the record are of a set length.  Then there are fields that are not mandatory and appear only if data has been entered which creates my problem.  Example:

       

      02FT0001  P 100020020613F1B0112D000001000000140000123456789ROBINS FEDERAL CU001123456789HBRSTONE CU TACCTRD1234567RONALD ANCDEI RONALD ANDITH                                                                                02FT0002  P 100020020613F1B0112D000002000000075000123456789ROBINS FEDERAL CU002*123456789GLOBAL CU SPOKCTRD123456 CKGABCDEFGH T IJKL* ABCDEFGH T IJKL  These are actually 2 lines.  The lines do not wrap but once you get to field the lengths are inconsistent and not necessarily will each line have the same fields.  I have good trapping characters for each field with the fields divided by , but I keep running into problems trying to separate the inconsistent fields in my template to get them to line up.  Can anyone help me with this??  Thanks!!!

        • Trapping fields of inconsistent lengths
          Grant Perkins

          Carrie,

           

          I think that this MAY call for "Voodoo Monarch" techniques and possibly a little bit of MSRP program processing depending upon what you want to get out of the record data string.

           

          Get the fields you can at the beginning of the line where they are consistent.

           

          The take the rest of the line as a single field and work out how you might be able to split it up into the fields you need using functions like LSPLIT, RSPLIT, INSTR and so on.

           

          In the meantime I will think about whether there are other approaches that might work. It would be useful to know which version of Monarch you have available and whether the numbers in the brackets are significant and appear consistemtly and in the same order (but with different data lengths as you have indicated already).

           

          Best regards,

           

          Grant

           

          Originally posted by Carrie McCall:

          I'm new to Monarch, and I'm trying to put our daily wires (we are a credit union) into a database using Monarch to separate the fields because there are inconsistent field lengths.  Each record has some manadatory fields which are at the beginning of the record are of a set length.  Then there are fields that are not mandatory and appear only if data has been entered which creates my problem.  Example:

           

          02FT0001  P 100020020613F1B0112D000001000000140000123456789ROBINS FEDERAL CU001123456789HBRSTONE CU TACCTRD1234567RONALD ANCDEI RONALD ANDITH                                                                                02FT0002  P 100020020613F1B0112D000002000000075000123456789ROBINS FEDERAL CU002*123456789GLOBAL CU SPOKCTRD123456 CKGABCDEFGH T IJKL* ABCDEFGH T IJKL  These are actually 2 lines.  The lines do not wrap but once you get to field the lengths are inconsistent and not necessarily will each line have the same fields.  I have good trapping characters for each field with the fields divided by , but I keep running into problems trying to separate the inconsistent fields in my template to get them to line up.  Can anyone help me with this??  Thanks!!! [/b][/quote]

          • Trapping fields of inconsistent lengths
            Carrie McCall

            Thank you!  I'm using Monarh 6 Pro version.  The numbers in the brackets represent the field.  For instance the string following is the financial institution that is sending the wire.  Each bracketed number represents a new field, and I can tell what field it is according to the bracketed number.  I would like for my final result not to show those field numbers, only the actual data, if that helps any.  Thanks again for your help!!

            • Trapping fields of inconsistent lengths
              Grant Perkins

              Carrie,

               

              OK, here goes.

               

              At the moment I only have access to a PC with the 5.02 version rather than V6 Pro installed but I don't think we need to use any V6 features to get what you need.

               

              There are other approaches to this sort of problem but I think we can stay with something quite straightforward here, assuming that the records have the same fields in the same order on the line. If the fields after the 3400 field can be different OR can appear in a different order, life would be a little more complex.

               

              I have assumed you have a template that gets the lines and can identify the first 6 fields of each row without problems. Here's what to do next.

               

              Starting at select the rest of the row into a single field which needs ot be as many characters wide as you are likely to find on the report. I will call this field G.

               

              Go to table view and create calculated field that will split field G into separate parts for the next stage.

               

              LSplit(G,5,"{",2)

               

              Splits field G into 5 parts at each appearance of the character "{" and, in this case, returns the values in the second part of the split field. I called this new field G_1. Note that we have to allow for the very fist "{" which will give a blank field we need to ignore.

               

              G_1     LSplit(G,5,"{",")     =     3400}123456789HBRSTONE CU TAC*

               

               

              Duplicate this field to get the other 3 values you require, changing the names and formulae as you go. Mqke sure the field definiiton allows for appropriate data width and display.

               

              G_2            LSplit(G,5,"{",3)          =           3600}CTR 

               

              G_3     LSplit(G,5,"{",4)     =     4200}D1234567RONALD ANCDEI           

               

              G_4     LSplit(G,5,"{",5)     =     5000} RONALD ANDITH    

               

              Then for each of these new fields make another split to remove the data required from the field reference.

               

              For example to change the G_1 field to drop the "3400}" string at the start and dreate a new field called '3400';

               

              LSplit(G_1,2,"}",2)

               

              will do the job.

               

              Make new fields for the G_2, G_3 and G_4 in the same way, naming them as you wish. (Note that there are several functions and methods you could use to do this.)

               

              LSplit(G_2,2,"}",2)

              LSplit(G_3,2,"}",2)

              LSplit(G_4,2,"}",2)

               

              Now that the basis of the method is outlined you can also see that the the '3400' field can in fact be created in one step rather than two.

               

              If we take the definition of '3400'  - LSplit(G_1,2,"}",2) - and substitute the formula for creating the G_1 field in place of the G_1 text, you get

               

              LSplit((LSplit(G,5,"{",2)),2,"}",2)

               

              The other fields can be defined using the same principles.

               

              If the variable width fields also have the possibility of being different data components completely of the same components but can appear in a different order, then we need to go in a little deeper and find some extra techniques. But for now this seems t give you what you are looking for.

               

              Let us know how you get on. If you get stuck send me a Private Message with your email address and I will provide the model I have created so that you can play with it yourself. (But you can also cut and paste the formulae from this post into the calculated field definitions ...)

               

              Best regards.

               

              Grant

               

               

                Originally posted by Carrie McCall:

              Thank you!  I'm using Monarh 6 Pro version.  The numbers in the brackets represent the field.  For instance the string following is the financial institution that is sending the wire.  Each bracketed number represents a new field, and I can tell what field it is according to the bracketed number.  I would like for my final result not to show those field numbers, only the actual data, if that helps any.  Thanks again for your help!! [/b][/quote]

               

              [size="1"][ September 17, 2003, 08:04 PM: Message edited by: Grant Perkins ][/size]

              • Trapping fields of inconsistent lengths
                Carrie McCall

                This has helped me immensely!  Thank you so much.  One more question and I think I will be done with my template.  Is there a way to have a field in a template for a report field that does not occur in every record?  In our case a reference is optional, but sometimes the reference includes important information that we would like to extract.  When I separate it as a field in my template I only have 2 records that include that field in my report but each record repeats the data from the record above it until the next record that actually has a value for that field.  I noticed in the field properties a button for copying from the field above it, and I made sure that I do not have that checked, but it's still copying.  Is there anything I can do about this?  Thanks!

                • Trapping fields of inconsistent lengths
                  Grant Perkins

                  Carrie,

                   

                  Excellent news!

                   

                  The question about the reference field is interesting. Is this field part of the same row as the rest of the data and, if so, where is it and are you able to select it as part of the detail template?  What you describe sounds like the result of using an append template as well as the detail template.

                   

                  Can you post a sample of the data with the reference field included together with the information about how to identify it - assuming it is not very obvious!! (It may be a separate line for example?)

                   

                  Once again there may be a few way to approach this depending upon how it all looks.

                   

                  Regards,

                   

                  Grant

                   

                  Originally posted by Carrie McCall:

                  This has helped me immensely!  Thank you so much.  One more question and I think I will be done with my template.  Is there a way to have a field in a template for a report field that does not occur in every record?  In our case a reference is optional, but sometimes the reference includes important information that we would like to extract.  When I separate it as a field in my template I only have 2 records that include that field in my report but each record repeats the data from the record above it until the next record that actually has a value for that field.  I noticed in the field properties a button for copying from the field above it, and I made sure that I do not have that checked, but it's still copying.  Is there anything I can do about this?  Thanks! [/b][/quote]

                  • Trapping fields of inconsistent lengths
                    Carrie McCall

                    Ok, here's 2 rows of data one with the field then one without.  Again, the copy and paste here loses a little, because these are actually 2 one-line records.  None of my data is on multiple lines.  I was using an append template for this field.  The field in question is and as I said earlier it copies the data in that field to each record below it until another record has data in that field.  Thanks again for all your help. Carrie 02P N12345678FT0120020613F1B0112D00001206131542FT01100020020613F1QCGMBC000079000010000000123456789GA CENTRAL CU000043651123456789RFCUCTRD123456RFCU*FED FUNDING* RFCUD123456789ROBINS FCUATTN: SUPERVISORY COMMP O BOX 2368WARNER ROBINS GA 31099* 02P N12345678FT0120020613F1B0112D00001306131555FT01100020020613L5QFA01D000045000000220000123456789ALASKA USA FCU123456789RFCUCTRD1234567 LOANCHRISTINA SMITH SAMEATTN: PAYOFF LOAN REMAINDER IN CKG*

                    • Trapping fields of inconsistent lengths
                      Grant Perkins

                      Carrie,

                       

                      I can think of a couple of ways to approach this though, as I am sure you have guessed, the answers start to get a little more complex.

                       

                      I also note that the field 'numbers' are different in these samples and the lines are different to each other. This suggests (maybe wrongly?) that simply splitting the data row into columns will not always produce the appropriate information in each column. In which case we need to seek an enhanced version of the basic solution worked out so far in order to deal with the extra variability.

                       

                      Am I getting concerned about things that are not significant?

                       

                      I am also interested to know what method you used to trap for the field as an append. Presumably it required a floating trap? More challenges!      One option (not very elegant) might be to use you existing append template. Add a new calculated field to check if the string exists in the "G" field. If it does then set a flag. Create another calculated field and use and IF function to decide whether to populate it with the value from the append field or make it blank.

                       

                      However, the 'extra' field also means that the original split needs to extend by one field (at least). So things are a little messier than they were which suggests having to revise the approach somewhat.

                       

                      What do you think?

                       

                      As I am a few hours ahead of you in time zones I may not be able to offer any further input today but, if you have an opportunity to respond, I may be able to make some progress early tomorrow in time for your start of day.

                       

                      Have fun.

                       

                      Grant

                       

                      Originally posted by Carrie McCall:

                      Ok, here's 2 rows of data one with the field then one without.  Again, the copy and paste here loses a little, because these are actually 2 one-line records.  None of my data is on multiple lines.  I was using an append template for this field.  The field in question is and as I said earlier it copies the data in that field to each record below it until another record has data in that field.  Thanks again for all your help. Carrie 02P N12345678FT0120020613F1B0112D00001206131542FT01100020020613F1QCGMBC000079000010000000123456789GA CENTRAL CU000043651123456789RFCUCTRD123456RFCU*FED FUNDING* RFCUD123456789ROBINS FCUATTN: SUPERVISORY COMMP O BOX 2368WARNER ROBINS GA 31099* 02P N12345678FT0120020613F1B0112D00001306131555FT01100020020613L5QFA01D000045000000220000123456789ALASKA USA FCU123456789RFCUCTRD1234567 LOANCHRISTINA SMITH SAMEATTN: PAYOFF LOAN REMAINDER IN CKG* /b[/quote]

                      • Trapping fields of inconsistent lengths
                        Carrie McCall

                        It is my fault that you are confused about the variability of the samples.  I apologize.  I'm actually working on 2 models, one for outgoing wires and one for incoming, and I just copied the records from whichever I was working with at the time. I see now that I copied one record from each. As you noticed because the fields are slightly different, that's why I have to do 2 models, but the concepts are the same.  Both have this reference field in them that can be included or may not be.  I do like your idea about using the flag.  That sounds like a good idea, and I'm certainly not looking for elegance!  I'm merely trying to get data in an Access database that we have not previously been able to store as our wire system is rather antiquated and does not store history for search purposes.   This way our accounting employees that do our wires can research items for our customers when the need arises and can do analysis and reports based on our wire history.  Probably more than you needed to know, but I tend to give more info than is necessary.  Yes I am using a floating trap for that field.  Thanks so much for your help.  This has helped me complete a project in a couple of days that I thought would take forever!

                         

                        Carrie

                        • Trapping fields of inconsistent lengths
                          Grant Perkins

                          Carrie,

                           

                          No problem about the confusion. I sort of guessed that was the situation but am grateful to have that confirmed!

                           

                          Anyway, here we have a

                           

                          New Solution:

                           

                          Note that this method does NOT require the use of an APPEND template. I think I got the various parts and adjustment values right but that is not too important  -  if you can follow the concept you should be able to make the appropriate adjustments if I got something wrong!

                           

                          -


                           

                          Start

                           

                          Map the fields that never change position then from the start of the 'variable' section select the rest of the line. Just as before so far.

                           

                          Each required field can be identified by "" but we MAY have variable lengths of data.

                           

                          We may also have one (or more?) fields appearing irregularly in the row in the middle of the other fields.

                           

                          The EXTRACT function (V6 function) may work well. But if not the following will work.

                           

                          Identify the start position of each field identifier, , and so on,  in our 'master field', G, using INSTR to make a calculated field Instr("",G)  this will give a NUMERIC field.

                           

                          This would give a field that I might refer to in what follows as or , etc.

                           

                          Add 6 to this number (in this case) to get the start position of the field DATA - i.e. exclude the field identifier unless it is of use in the data.

                           

                          Instr("",G)+6

                           

                          This would give a field that I might refer to in what follows as . etc,.

                          Do this for each field.

                           

                          Now, for each field, create a new calculated field to work out how many characters are to be picked for the field.

                           

                          For example

                           

                          -[3600 Data Start]

                           

                          will give the number of characters between the two identifiers.

                           

                          As would -(+6)  !!

                           

                          To extract the information we can use the SUBSTR function.  (In the next example I have omitted the calculation of the number of characters as a separate field and simply included it as part of the formula.

                           

                          Substr(G,[3600 Data Start],(-[3600 Data Start]))

                           

                          So, this is telling the system to select from field G, starting at the beginning ot the data space for the field 3600, for the number of characters from that start point to the beginning of the next field identifier.

                           

                          Do that for each field and everything should be fine. The last field is a bit more interesting as there is no end marker. Either make the calculation from the known maximum field size based on the width of your 'master' field or, as an alternative, consider using RSPLIT instead (back to the first "}" found, 2 parts, select the first part).

                           

                          So everything done except for the 'sometimes included' field(s).

                           

                          In the example we know where the field is likely to be in the array.( If it was at the end line it would be easier!)

                           

                          As we  know where it is we only need to know how to deal with it and the field immediately before it. All the other fields have a constant relationship with each other for our purposes.

                           

                          If we have already created a calculated field to check for the existence of this occasional field , we will have a known starting point for it ( for example). So we can check the length of the field upto the field that will follow in in the normal way BUT also leave it blank if the field does not exist in this record. The IF part of the following formula ensures that we get a blank entry if the field ( in this example) does not exist in this record.

                           

                          if(>0,(Substr(G,[4320 Data Start],(-[4320 Data Start]))),"")

                           

                          The field BEFORE 4320 is always 4200 in this example. To get the length of 4200 we need to measure to the start of 5000 OR 4320 if 4320 exists. Another IF statement will acheive this.

                           

                          if(>0,((Substr(G,[4200 Data Start],(-[4200 Data Start])))),(Substr(G,[4200 Data Start],(-[4200 Data Start]))))

                           

                          Whilst this looks a little complex it is quite straightforward and would look simpler of we were pre-calculating the 'length of field' values rather than making them part of the formula.

                           

                          Basically there are 2 outcomes possible. If our 4320 field exists in this row, calculate using it

                           

                          (-[4200 Data Start])

                           

                          and if it doesn't, then use the 5000 field instead.

                           

                          (-[4200 Data Start]).

                           

                          Obviously of you have more than one possible occasional field things can get more interesting but most likely would still be acheivable although the nested IF statements might become rather complex.

                           

                          I am not yet able to check other options in V6 and V7 - only have a V5 system installed on a working machine at the moment at my current location.

                           

                          However I think V6 may offer some alternative approaches using EXTRACT and V7 through using STRIP (to get rid of the occasional field in a new version of the 'master' field for example) or STUFF to always insert an entry for the occasional field but leave it blank. Any approach that make the majority of the processing easier to understand (and therefore maintain) are worth considering.

                           

                          I hope this all makes sense to you. I would suggest that it is easier to make all of the calculation stages separately in the first pass so that you can get comfortable with what is going  Later the more complicated formulae can be constructed from the component parts if you wish and the other fields can be deleted once they are no longer required. Using the "Duplicate" function for generating variations of an existing calculated field definition means that this can be a reletively quick process.

                           

                          I hope this helps a little more.

                           

                          Best regards,

                           

                          Grant

                           

                           

                          Originally posted by Carrie McCall:

                          It is my fault that you are confused about the variability of the samples.  I apologize.  I'm actually working on 2 models, one for outgoing wires and one for incoming, and I just copied the records from whichever I was working with at the time. I see now that I copied one record from each. As you noticed because the fields are slightly different, that's why I have to do 2 models, but the concepts are the same.  Both have this reference field in them that can be included or may not be.  I do like your idea about using the flag.  That sounds like a good idea, and I'm certainly not looking for elegance!  I'm merely trying to get data in an Access database that we have not previously been able to store as our wire system is rather antiquated and does not store history for search purposes.   This way our accounting employees that do our wires can research items for our customers when the need arises and can do analysis and reports based on our wire history.  Probably more than you needed to know, but I tend to give more info than is necessary.  Yes I am using a floating trap for that field.  Thanks so much for your help.  This has helped me complete a project in a couple of days that I thought would take forever!

                           

                          Carrie /b[/quote]

                          • Trapping fields of inconsistent lengths
                            Carrie McCall

                            Grant,

                             

                            Just wanted you to know that your instructions were wonderful and I was able to isolate that variable field using the "G" field where it does not repeat values in subsequent fields.  I'm now finished with my models and I can't begin to tell you how much I appreciate your help.  I'm not sure I could have gotten through it otherwise, or at least not nearly as quickly.  Thanks again!!

                             

                            Carrie

                            • Trapping fields of inconsistent lengths
                              Grant Perkins

                              Carrie,

                               

                              Glad I could help.

                               

                              There are quite a few things that can be achieved with that approach as you have probably already recognised.

                               

                              I saw that a few of the other fields looked like they had some interesting bits of information embedded - dates and stuff for sure. Maybe some of them could be usefully expanded as well ... OK, perhaps not now, but it is easy to get carried away with the ideas! 

                               

                              Have fun.

                               

                              Grant

                              Originally posted by Carrie McCall:

                              Grant,

                               

                              Just wanted you to know that your instructions were wonderful and I was able to isolate that variable field using the "G" field where it does not repeat values in subsequent fields.  I'm now finished with my models and I can't begin to tell you how much I appreciate your help.  I'm not sure I could have gotten through it otherwise, or at least not nearly as quickly.  Thanks again!!

                               

                              Carrie /b[/quote]