Split one field into 2 numeric values

[lany]

I am capturing some detail on line 1, and some more fields that follow on the second line. My records are always 2 lines long. In the second line there are 2 fields I'm capturing. I think the only way to get the data on the second line is to just capture it in one field to start (upon reading some of the threads here and using the "start on last field defined" field option) and then should I use a function to split out these two fields?

Here is what my data looks like. Note that I don't care to capture the 0.00's, but that I think is what's holding me back from just capturing the data with a two line template in the first place. Maybe I am wrong is there is an easier route.

CRD CHG DESCRIPTION
AMOUNT1 AMOUNT2
40 4141 EIRF 0.00 0.00
100.00 -50.00
40 4242 EIRT 0.00 0.00
200.00 25.00

[RalphB]

Hi lany,

Have you thought about capturing the second line as your detail template and your first line as an append? This way you can define each field you need seperately.

I think this would be the simplest and easiest way to extract the data.

[lany]

I would love to be able to do that, but with your question, I realize I left out a detail in my problem. There are some subtotal and total lines that are unavoidable to capture in the second line. So then it's grabbing the second detail line but there is no matching first line. So my records get out of whack.

[Data Kruncher]

lany,

Just to clarify, do the values at the end of the first line always contain two sets of "0.00", or do they contain other values in the real report, such as 7.69?

I think that you'll be able to use a two line template. The details of what to capture and how to handle the data might change depending upon the answer to the question.

If they're always literally "0.00" then just don't bother capturing them.

If they do have values, but you don't want include the zeros in your data, then capture those fields and filter out the records with zeros in the table.

Is there a clear way to determine which records are the totals? Can you avoid including them in the captured data, or filter them out if you must capture them along with the regular details?

[lany]

They sometimes have real numbers. and sorry, if it makes a difference those numbers in the first line that I don't care about are always whole numbers like 0, 1, or 2 etc.

For the total lines, they always say "total" or "subtotal" in the line, so the only way I can figure out how to get rid of them is with a filter looking for those strings. But in the meantime when it grabs these records, it throws off the sequence of how the append line is lining up with the detail. So hear is a more comprehensive example of what my report looks like

MC 50 1213 EIRF 1 0
51.00 5.00 2.25%
MC 50 SUBTOTAL 1 0 (I want to ignore this line)
51.00 5.00 (ignore)
MDBT 52 1213 EIRFMT 3 0
86.00 .00 0.00%
MDBT 52 SUBTOTAL 3 0 (ignore)
86.00 .00 (ignore)
MC TOTAL 4 0 (ignore)
137.00 .00 (ignore)

I want to the output to be
MC 50 1213 EIRF 51.00 5.00
MDBT 52 1213 EIRFMT 86.00 0.00

[Data Kruncher]

OK, definitely use a two line detail template; no appends.

Trap each line as a single field, Line1 and Line2.

Filter out the totals:
Instr("TOTAL",Line1)=0

then use a few calculated fields with LSplit (splitting on spaces) to extract your individual fields from Line1 and Line2.

I'm sure that you'll be done in no time.

[lany]

Thank you! The two line detail template worked. One last obstacle that I am stuck on!

Unfortunately, the first character field is either 2 or 4 characters. So, the ones that are 2 characters have spaces after them to mess up with my lsplit function. There are also other fields that have spaces in the field itself. If there another function I can use to split that line into fields based on position in the the line, rather than space separators?
Here is the type of data I have after putting it into the 2 line field.

VISA 40 1213 EIRF/MERT1 0 0 .00 .00
MC 50 1213 EIRF/RET C12 0 0 .00 .00

[lany]

Thanks again for all the help. I eventually arrived at my solution which was to do the one line detail trap, using a 2 single line field to catch the second line stuff. I cleaned up the varying space issues I had using Intrim function (didn't know it existed, but great function to eliminate unwanted excess spaces) and the extracted the fields I wanted using Lsplit. Great learning experience and of course, outcome. Thanks again for the help here.