7 Replies Latest reply: May 15, 2014 10:02 AM by lany _ RSS

    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

        • Split one field into 2 numeric values
          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.

            • Split one field into 2 numeric values
              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.

                • Split one field into 2 numeric values
                  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?

                    • Split one field into 2 numeric values
                      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

                        • Split one field into 2 numeric values
                          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[/CODE]

                           

                          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.

                            • Split one field into 2 numeric values
                              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

                                • Split one field into 2 numeric values
                                  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.