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:

{1500}02FT0001 P *{1510}1000{1520}20020613F1B0112D000001{2000}00000 0140000{3100}123456789ROBINS FEDERAL CU*{3320}001*{3400}123456789HBRSTONE CU TAC*{3600}CTR{4200}D1234567*RONALD ANCDEI*{5000} *RONALD ANDITH* {1500}02FT0002 P *{1510}1000{1520}20020613F1B0112D000002{2000}00000 0075000{3100}123456789ROBINS FEDERAL CU*{3320}002*{3400}123456789GLOBAL CU SPOK*{3600}CTR{4200}D123456 CKG*ABCDEFGH T IJKL*{5000} *ABCDEFGH T IJKL*

These are actually 2 lines. The lines do not wrap but once you get to field {3400} 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 {XXXX}, 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!!!

[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:

{1500}02FT0001 P *{1510}1000{1520}20020613F1B0112D000001{2000}00000 0140000{3100}123456789ROBINS FEDERAL CU*{3320}001*{3400}123456789HBRSTONE CU TAC*{3600}CTR{4200}D1234567*RONALD ANCDEI*{5000} *RONALD ANDITH* {1500}02FT0002 P *{1510}1000{1520}20020613F1B0112D000002{2000}00000 0075000{3100}123456789ROBINS FEDERAL CU*{3320}002*{3400}123456789GLOBAL CU SPOK*{3600}CTR{4200}D123456 CKG*ABCDEFGH T IJKL*{5000} *ABCDEFGH T IJKL*

These are actually 2 lines. The lines do not wrap but once you get to field {3400} 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 {XXXX}, 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!!!

[Carrie McCall]

Thank you! I'm using Monarh 6 Pro version. The numbers in the brackets represent the field. For instance the string following {3100} 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!!

[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 {3400} 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}D1234567*RONALD 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 {3100} 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!!

[ September 17, 2003, 08:04 PM: Message edited by: Grant Perkins ]

[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!

[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!

[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 {4320} 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

{1100}02P N{1110}12345678FT01{1120}20020613F1B0112D000012061 31542FT01{1510}1000{1520}20020613F1QCGMBC000079{20 00}000010000000{3100}123456789GA CENTRAL CU*{3320}000043651*{3400}123456789RFCU*{3600}CTR{4 200}D123456*RFCU*{4320}FED FUNDING*{5000} *RFCU*{5100}D123456789*ROBINS FCU*ATTN: SUPERVISORY COMM*P O BOX 2368*WARNER ROBINS GA 31099*
{1100}02P N{1110}12345678FT01{1120}20020613F1B0112D000013061 31555FT01{1510}1000{1520}20020613L5QFA01D000045{20 00}000000220000{3100}123456789ALASKA USA FCU*{3400}123456789RFCU*{3600}CTR{4200}D1234567 LOAN*CHRISTINA SMITH*{5000} *SAME*{6500}ATTN: PAYOFF LOAN REMAINDER IN CKG*

[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 {4320} 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 {4320} 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 {4320} 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

{1100}02P N{1110}12345678FT01{1120}20020613F1B0112D000012061 31542FT01{1510}1000{1520}20020613F1QCGMBC000079{20 00}000010000000{3100}123456789GA CENTRAL CU*{3320}000043651*{3400}123456789RFCU*{3600}CTR{4 200}D123456*RFCU*{4320}FED FUNDING*{5000} *RFCU*{5100}D123456789*ROBINS FCU*ATTN: SUPERVISORY COMM*P O BOX 2368*WARNER ROBINS GA 31099*
{1100}02P N{1110}12345678FT01{1120}20020613F1B0112D000013061 31555FT01{1510}1000{1520}20020613L5QFA01D000045{20 00}000000220000{3100}123456789ALASKA USA FCU*{3400}123456789RFCU*{3600}CTR{4200}D1234567 LOAN*CHRISTINA SMITH*{5000} *SAME*{6500}ATTN: PAYOFF LOAN REMAINDER IN CKG*

[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

[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 "{xxxx}" 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, {3400}, {5000} and so on, in our 'master field', G, using INSTR to make a calculated field

Instr("{3400}",G) this will give a NUMERIC field.

This would give a field that I might refer to in what follows as [3400 Start] or [3600 Start], 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("{3400}",G)+6

This would give a field that I might refer to in what follows as [3400 Data Start]. 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

[4200 start]-[3600 Data Start]

will give the number of characters between the two identifiers.

As would [4200 start]-([3600 Start]+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],([4200 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 ([4320 Start] 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 ({4320} in this example) does not exist in this record.

if([4320 Start]>0,(Substr(G,[4320 Data Start],([5000 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([4320 Start]>0,((Substr(G,[4200 Data Start],([4320 Start]-[4200 Data Start])))),(Substr(G,[4200 Data Start],([5000 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

([4320 Start]-[4200 Data Start])

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

([5000 Start]-[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

[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

[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