2 Replies Latest reply: May 15, 2014 10:06 AM by GuinnessDrinker _ RSS

    Text Report Problem

    GuinnessDrinker _

      Cost Centre ABCD Line Rental VAT Free CallsVATable Calls Other

      12345678912 ABCDEF J 0.0000 0.0000 0.0658 0.0000             

      23456789012 ABCDEFGHI S 0.0000 0.0000 4.0750 0.0000          

      34567890123 BCDEFGHIJ K 0.0000 0.0000 18.7882 0.0000         

      Subtotals ex VAT 0.0000 0.0000 22.9290 0.0000                

      VAT 0.0000 0.0000 4.0126 0.0000                              

      TOTAL inc VAT 0.0000 0.0000 26.9416 0.0000                   

      COST CENTRE TOTAL 26.9416                                    

      /CODE

                          

      In the above report of a mobile phone bill, I am trying to separate the fields.

      Below is the report as PDF

       

      Cost Centre                 ABCD                                                                                Line   Rental               VATable Calls                  VAT Free Calls                                 Other                                                                               

      12345678912                    ABCDEFGH M                                                                                0.0000                           7.5555                          0.0000                           0.0000                    

      /CODE

       

      The reason I have converted to text is that the data on the PDF changes position every month enabling the resulting model which has other calculations within monarch useless So I am trying text files to see if the data positioning is more stable

      The problem is I cannot seem to trap the data with floating trap. Have read the book and searched the forum but have had no success in solving my problem

       

      The Cost centre is the ABCD next to the title Cost Centre

      The next row starts phone number (12345678912) the next field is name and initial (ABCDEF J)

      The rest is as the top row titles Line Rental etc.

      Can anyone help

        • Text Report Problem
          Data Kruncher

          Hi GD,

           

          As I recommended earlier today, if this is an accurate depiction of your text file, I'd avoid trying the floating trap and instead paint the entire line as a single field, again named "A".

           

          PhoneNumber becomes a calculated field:

          LSplit(A,7," ",1)[/code]LName:

          LSplit(A,7," ",2)[/code]Inital:

          LSplit(A,7," ",3)[/code]and the numeric fields LineRental:

          Val(LSplit(A,7," ",4))[/code]VATFree:

          Val(LSplit(A,7," ",5))[/code]CallVATable:

          Val(LSplit(A,7," ",6))[/code]CallsOther:

          Val(LSplit(A,7," ",7))[/code]Then just append the Cost Centre label.

           

          Combine the LName and Initial with LName" "Initial

           

          HTH,

          Kruncher