Multi Line Trap Confusion

[Gene]

I'm having issues.

I've got a report where sometimes the record is one line long, other times it may be 6 or 7 lines long.
I've gone thru multiple other posts on the forum regarding multiple line traps, however, I'm not getting the data to look correct after trapping.

My report resembles this:


5000009161204443790061007326
5500 696994354003100097500200994354310002 0
5300000019500000000000000000
59700000000000000000000000000000000000000000000000 00
600000001950
609900000000
4100009163408007326
4000009163412000023
540000900023163436 696994313150
540000900023163522 696994356150
4100009163525000023
4000009163555007326
5000009164309443820061007326
5500 693194328000500000000200994328050002 0
5300000000000000000000000000
59700000000000000000000000000000000000000000000000 00
600000000000
609900000000
4100009171841007326
Each new record starts when a number appears in the first column. Depending on the first 4 digits of this line, there may or may not be more lines below (all of which are indented one spot).

The sample report I'm using has 85 records (there is 315 similar reports I'm going to load once I figure out how to get the model working correctly.) However, I've tried detail and append, using the preceding string as a blank for the successive lines, and several other items. I never get the correct number of records returned.

I don't need to trap any real fields (I'm going to break out each line afterwards because the data is in different spots depending on those first 4 digits.)

Can anyone point me in a direction?

Thanks,
Gene

[ September 03, 2003, 08:47 AM: Message edited by: Gene ]

[Grant Perkins]

Gene,

C&P of your sample, making sure that the spaces exist at the start of the lines as you indicate (they got lost in the C&P somewhere!) and then trying a few things, got me to this. It seems to be what you are seeking. (Using 5.02)

Your detail template is for the non-indented lines. So I simply set the template to pick lines with a numeric at column 1 and then the whole line for the data.

Then select the same line as above plus one below it for the next template (I used lines 1 and 2 from the sample). Use the same selection criterion for the template but ignore line 1 and select the whole possible width (except the first space column) of the second line as the field.

Go to Field Definition>>Options and set the "Start Field On" option to "Preceding string" with the single blank, and the "End Field On" option to "minimum action only". Note that on the sample any of the options except Line Count will probably be OK.

Then identify the template as a FOOTER.

That seems to do it.

Then you have the simple job of splitting the fields into the appropriate fields!

Hope it works for you the way the way it looks like it works for me.

Good luck.

Grant



Originally posted by Gene:
I'm having issues.

I've got a report where sometimes the record is one line long, other times it may be 6 or 7 lines long.
I've gone thru multiple other posts on the forum regarding multiple line traps, however, I'm not getting the data to look correct after trapping.

My report resembles this:


5000009161204443790061007326
5500 696994354003100097500200994354310002 0
5300000019500000000000000000
59700000000000000000000000000000000000000000000000 00
600000001950
609900000000
4100009163408007326
4000009163412000023
540000900023163436 696994313150
540000900023163522 696994356150
4100009163525000023
4000009163555007326
5000009164309443820061007326
5500 693194328000500000000200994328050002 0
5300000000000000000000000000
59700000000000000000000000000000000000000000000000 00
600000000000
609900000000
4100009171841007326
Each new record starts when a number appears in the first column. Depending on the first 4 digits of this line, there may or may not be more lines below (all of which are indented one spot).

The sample report I'm using has 85 records (there is 315 similar reports I'm going to load once I figure out how to get the model working correctly.) However, I've tried detail and append, using the preceding string as a blank for the successive lines, and several other items. I never get the correct number of records returned.

I don't need to trap any real fields (I'm going to break out each line afterwards because the data is in different spots depending on those first 4 digits.)

Can anyone point me in a direction?

Thanks,
Gene

[ September 03, 2003, 01:18 PM: Message edited by: Grant Perkins ]

[Gene]

Grant:

Thanks for the help, I was having I was trying to make one huge detail, and it wasn't working very well.

Thank you very much.

[Gene]

Grant can I bother you one more time?

We realized that when going thru the reports, if the transaction involved a credit card, the info appears above the transaction.
The CC line always begins with 3100.

I tried to append this to the other detail info already grabbed, however some records do not have the field to append, and the append fills in to too many records.

I had set the detail and footer trap to look for numerics in the first 5 positions, thereby eliminating the credit card lines, and set a trap for the 3100 to grab the credit card lines.

Am I making this more complicated than I need to?

New Sample

3100>B:G7gZ&RPA.8L(>|}}}01596100000975
5000009221521444240081000023
5500 693194330003100097500100994330310001 0
5300000009750000000000000000
59700000000000000000000000000000000000000000000000 00
600100000975
6100>B:G7gZ&RPA.8L(>|}}}A00000000
6150S0159610101NAME/NAME 00000000
609900000000
3100=A9F9bX)UT=2;J%=~}|} 00001950
5200009221553444250071000023
5500 6931943300031000975002 0
5300000019500000000000000000
59700000000000000000000000000000000000000000000000 00
600100001950
6100=A9F9bX)UT=2;J%=~}|}A00000000
6150S 0101NAME/NAME 00000000
5501009222252 6931943300030000000001000023 I desperately need to take an advanced training class smile.gif

[Grant Perkins]

Gene,

Certainly does complicate things a bit - not sure if the forum allows subsidiary questions of this nature! ;)

One 'dirty' route would be to simply append the data for the 3100 CC line and then keep it or eliminate it using a formula field to compare if with some other data. For example, looking at the new sample, seems like every CC transcation MUST HAVE something in the 'footer' field. So we could compare on that and exclude the CC data if the footer is empty.

However I don't think that will cover all the situations you will find in the data so there may need to be several such comparisons.

I'm going to play around some more and I also have a look back through the archives 'cos something is nagging me about seeing a good solution to a very similar problem a while back.

If all else fails (and depending upon which version of Monarch you are using) it may be that we have to look at the good old "2 stage" solution using a lookup table (either external if you have V5 or above Pro version) or internal if you have standard.

For that I think, off the top of my head, I would look to extract the 3100 lines into a separate table but I would need to relate them to a transaction reference number or something so that when we run the second stage (what you had before the new CC lines were recognised as an issue) you would then be able to read the lookup table and append the CC information to each record that had a relevant entry.

A few options and questions. I'll hope to have something for you tomorrow, one way or the other.

Or maybe some of our friends on the board will beat me to it?

All the best.

Grant


Originally posted by Gene:
Grant can I bother you one more time?

We realized that when going thru the reports, if the transaction involved a credit card, the info appears above the transaction.
The CC line always begins with 3100.

I tried to append this to the other detail info already grabbed, however some records do not have the field to append, and the append fills in to too many records.

I had set the detail and footer trap to look for numerics in the first 5 positions, thereby eliminating the credit card lines, and set a trap for the 3100 to grab the credit card lines.

Am I making this more complicated than I need to?

New Sample

3100>B:G7gZ&RPA.8L(>|}}}01596100000975
5000009221521444240081000023
5500 693194330003100097500100994330310001 0
5300000009750000000000000000
59700000000000000000000000000000000000000000000000 00
600100000975
6100>B:G7gZ&RPA.8L(>|}}}A00000000
6150S0159610101NAME/NAME 00000000
609900000000
3100=A9F9bX)UT=2;J%=~}|} 00001950
5200009221553444250071000023
5500 6931943300031000975002 0
5300000019500000000000000000
59700000000000000000000000000000000000000000000000 00
600100001950
6100=A9F9bX)UT=2;J%=~}|}A00000000
6150S 0101NAME/NAME 00000000
5501009222252 6931943300030000000001000023 I desperately need to take an advanced training class smile.gif

[ September 05, 2003, 11:00 AM: Message edited by: Grant Perkins ]

[Grant Perkins]

Gene,

Here is the 'rough and ready' solution to the Credit Card line problem. I make no claims of elegance.

Go for your solution that over-reports the association of the Credit Card Transaction details by associating it with additional erroneous lines.

The following section can probably be reduced to a single formula creating a single new data field. For simplicity of explanation I will present it as multiple fields and calculations. This has the merit of flexibility and easier reading, especially when generating a new model. Apologies if I appear to have described it overly simplistically. I think you may well be quite able to resolve this yourself but I have attempted to describe it in a way that others with less knowledge could also have a reasonable chance of following. (Did I succeed?)

Looking at the sample it seems that the 3100 line string is repeated in part down in the lines that we are extracting on the footer template. FOR THIS SOLUTION TO WORK THE ABOVE MUST ALWAYS BE TRUE AT LEAST FOR SOME PART OF THE LINE (AND THE STRING MUST BE UNIQUE ON THE LINE).

What we need to do is identify whether the 3100 line string does actually appear in the 'footer detail field' somewhere.

If the 'footer data' line(s) field is called SUBDATA and the Credit Card Line field is called CCLINE, the following formula will identify whether there is a match for part of the CCLINE field somewhere in the SUBDATA field. It will return either 0 (Zero, possibly displayed as blank) if there is no match OR the character position where the match actually begins in the SUBDATA string. You may already be using something similar to split up your collected 'footer' lines into the data fields you require.

if((Substr(CCLINE,1,12))<>" ",Instr((Substr(CCLINE,1,12)),SUBDATA),0)

OK, so the explanation is;

If the first 12 characters of the field CCLINE are not Blank, then use the INSTR function to return the character position number of the start of the matching string if it is matched but if CCLINE is an empty field return the value 0 (zero). The INSTR function may not find a match in which case it will also return a value of zero. (In this case the field nees to be defined as a numeric field. Monarch may well default to a Character field so take care as the formula will appear to fail.

So we now have a new calculated field which will either be blank (or zero) or have a number in it. The fields with numbers mean those are genuine Credit Card transaction records. So you can now correctly flag those records that are credit card transactions. I don't know what you may want to do with them but one simple way of creating a calculated field that is populated with the original information (contents of the CCLINE row for example) might be;

Assume we called the new calculated field from above IS_CCTRANS

if(IS_CCTrans>0,CCLINE,"")

Very simply, this IF function checks to see if the new field has a value greater than 0, if it does the additional new field is given the value fron the CCLINE field but if it the value is zero or less the new field will be left blank.

If you feel like playing with nested IF functions it should be possible to insert the first formula above into the second one to complete the step in one transaction.

if((if((Substr(CCLINE,1,12))<>" ",Instr((Substr(CCLINE,1,12)),SUBDATA),0))>0,CCLINE ,"")

should be quite close to getting it in one.

I hope this helps you to move foward.

Grant

PS - I will still have a look through the archives to seek a better answer.

[ September 05, 2003, 11:12 AM: Message edited by: Grant Perkins ]

[Gene]

Grant,
That worked great!

Thank you very much.

Gene

[Gene]

Working on something else and have bumped into this....

If there are empty spaces at the end of an indivdual line, Monarch doesn't pick them up.
Is there a way to force Monarch to keep these extra spaces?

So where the report shows:


5500 693194108000100095000205094108010002
5300000019000000000000000000
59700000000000000000000000000000000000000000000000 00
600000001900 Monarch grabs:


5500 693194108000100095000205094108010002 5300000019000000000000000000 59700000000000000000000000000000000000000000000000 00 600000001900 Do I need a change a setting, I would like to keep those blank spaces in there!

Thanks,
Gene

[Grant Perkins]

Gene,

This sounds like pretty much the same things as bickle (http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000289) has raised. It may look different but it seems very similar to me.

Certainly makes slice and dice of a field based on character position a bit more complex than looking at the source fiel data structure!

Looking for further inspiration ...

Grant


Originally posted by Gene:
Working on something else and have bumped into this....

If there are empty spaces at the end of an indivdual line, Monarch doesn't pick them up.
Is there a way to force Monarch to keep these extra spaces?

So where the report shows:


5500 693194108000100095000205094108010002
5300000019000000000000000000
59700000000000000000000000000000000000000000000000 00
600000001900 Monarch grabs:


5500 693194108000100095000205094108010002 5300000019000000000000000000 59700000000000000000000000000000000000000000000000 00 600000001900 Do I need a change a setting, I would like to keep those blank spaces in there!

Thanks,
Gene

[ May 19, 2006, 12:18 PM: Message edited by: Todd Niemi ]

[Gene]

Certainly does look similar. Not quite sure how I missed his post.
I think the only difference is, I'm using a mult line trap, so trying to add the trailing spaces based on the length of the field would add what spaces are at the very end of the field.
However, I also want to keep the correct number of spaces in the middle as well!

A neato little setting in Monarch would be nice.

Future release maybe?

[ September 26, 2003, 10:00 AM: Message edited by: Gene ]