5 Replies Latest reply: Aug 25, 2015 4:36 PM by Robert Hutchings RSS

    Loading a PDF text file with a minus after a number

    Robert Hutchings

      A company receive remittance advices in PDF format

      This is then saved the PDF as text and loaded to Qlik as shown below

      Works fine except for minus amounts

       

      But its in this format ---->?      1000- (number then minus sign)

      not -1000

       

      This causes problems. The minus is dropped and this number is joined to the next line

       

      How do I overcome this issue. Ive tried everything I can think of.

       

      Thanks

       

      Remittances:

      load

      DATE (date#(right (FileBaseName(),10),'DD.MM.YYYY')) AS QADDate,

         text (@1) as JDWRef,

          date (date#(@2,'DD.MM.YY')) as DateJDW,

            @3  as Amount

      FROM [lib://Misc/Payment Advice Note from 17.12.2014.txt]

      (txt, codepage is 1252, no labels, delimiter is spaces, no quotes)

      where

      date (date#(@2,'DD.MM.YY')) >1

      and  [@3] < 100000

      ;

       

      Remittances:

      load

      DATE (date#(right (FileBaseName(),10),'DD.MM.YYYY')) AS QADDate,

         text (@1) as JDWRef,

           date (date#(@3,'DD.MM.YY')) as DateJDW,

      @4  as Amount

      FROM [lib://Misc/Payment Advice Note from 17.12.2014.txt]

      (txt, codepage is 1252, no labels, delimiter is spaces, no quotes)

      where date (date#(@3,'DD.MM.YY')) >1 and [@4] < 100000

      ;

        • Re: Loading a minus after a number
          Maximiliano Velez

          Perhaps, you could post some sample data?

           

          Rgds

          • Re: Loading a minus after a number
            Sunny Talwar

            I am not 100% certain that I understand your issue. Do you want to see the negative sign after the number or is it disappearing? Not sure how you are handling the negative sign in the two scripts you have provided. I guess the best way to handle the issue would be to provide some sample data to look at.

             

            Best,

            Sunny

            • Re: Loading a minus after a number
              Sinan Ozdemir

              Maybe something like this:

              Capture.PNG

              The minus sign will be in front of the number:

              Capture.PNG

              Hope this helps.

                • Re: Loading a minus after a number
                  Robert Hutchings

                  Thanks for your replies

                   

                  I can see what the issue is now. Its to do with the way that a PDF file saves as text

                   

                  Here's an example of one page. The column headings on the page were

                   

                  ReferenceNum          Date                    Amount

                  2178617A                  02.09.2014           67.02-

                  2184008A                  03.09.2014          48.30-

                  ETC

                   

                  So the the minus sign is dropped and the amount is joined with the next row. Strange

                  (and doesn't reflect well on Adobe)

                   

                  2178617A 02.09.2014 67.012184008A

                  03.09.2014 48.302184348A

                  31.08.2014 140.222184510A

                  31.08.2014 308.272184516A

                  02.09.2014 131.002185371A

                  02.09.2014 107.002185744A

                  02.09.2014 160.022185969A

                  02.09.2014 228.602186301A

                  02.09.2014 149.932186447A

                  03.09.2014 76.202186677A

                  02.09.2014 195.722187265A

                  02.09.2014 105.772187394A

                  03.09.2014 143.46

                • Re: Loading a minus after a number
                  Robert Hutchings

                  There might be an easier way. But this works

                   

                  //LOAD PLUS BALANCES

                  Remitt:  //3 columns

                  load

                  RECNo () as RN,  //based on order in the remittance advice

                  DATE (date#(right (FileBaseName(),10),'DD.MM.YYYY')) AS QADDate,

                     text (PurgeChar(@1,'ABCD')) as JDWRef,

                      date (date#(@2,'DD.MM.YY')) as DateJDW,

                    @3  as Amount,

                      purgeCHAR (@4 , '-' )  as sAPRE

                   

                  FROM [lib://Misc/Payment Advice Note from 17.12.2014.txt]

                  (txt, codepage is 1252, no labels, delimiter is spaces, no quotes)

                  where

                  date (date#(@2,'DD.MM.YY')) >1

                  and  [@3] < 100000 and  len ([@3]) <> 0  and @3 <> 0

                  ;

                   

                  Remitt2:   ///4 columns

                  Concatenate (Remitt)

                  load

                  RECNo () AS RN,  //based on order in the remittance advice

                  DATE (date#(right (FileBaseName(),10),'DD.MM.YYYY')) AS QADDate,

                    text (PurgeChar(@1,'ABCD')) as JDWRef,

                       date (date#(@3,'DD.MM.YY')) as DateJDW,

                     (@4) as Amount

                  FROM [lib://Misc/Payment Advice Note from 17.12.2014.txt]

                  (txt, codepage is 1252, no labels, delimiter is spaces, no quotes)

                  where date (date#(@3,'DD.MM.YY')) >1 and [@4] < 100000 and len ([@4]) <> 0

                   

                  ;

                   

                  ///==============================================================================================

                  //LOAD MINUS BALANCES

                  Remitt3:   //1st negative on page

                  Concatenate (Remitt)

                  load

                  // RowNo(),

                  RECNo () AS RN,

                  RIGHT (@3 , LEN (@3)  -  INDEX (@3,'.') -2 )            as TEMP,

                  DATE (date#(right (FileBaseName(),10),'DD.MM.YYYY')) AS QADDate,

                  TEXT (PURGECHAR(@1,'ABCD')) as JDWRef,

                       date (date#(@2,'DD.MM.YY')) as DateJDW,

                  - LEFT (@3 , INDEX (@3,'.')+2)              as Amount

                  FROM [lib://Misc/Payment Advice Note from 17.12.2014.txt]

                  (txt, codepage is 1252, no labels, delimiter is spaces, no quotes)

                  where date (date#(@2,'DD.MM.YY')) >1

                  AND len( @3) >  10

                  and  - LEFT (@3 , INDEX (@3,'.')+2)   <> 0

                   

                  ;

                   

                  Remitt4:  //All but 1st negative on page

                  Concatenate (Remitt)

                  LOAD

                  RECNO() AS RN,  //based on order in the remittance advice

                  DATE (date#(right (FileBaseName(),10),'DD.MM.YYYY')) AS QADDate,

                  RIGHT (@2 , LEN (@2)   - INDEX (@2,'.')   -2  )         as TEMP,

                  NULL() as JDWRef,

                  date (date#(@1,'DD.MM.YY')) as DateJDW,

                  - LEFT (@2 , INDEX (@2,'.')+2)              as Amount

                   

                  FROM [lib://Misc/Payment Advice Note from 17.12.2014.txt]

                  (txt, codepage is 1252, no labels, delimiter is spaces, no quotes)

                  where date (date#(@1,'DD.MM.YY')) >1

                  and len ([@2]) < 1000 

                  and  - LEFT (@2 , INDEX (@2,'.')+2) <>0

                  ;

                   

                  //===========================================================================================

                  // calcualate JDW Ref when missing

                  //reorder based on order in remittance advice (RN)

                  TEMP:  //

                  Load

                  RN,

                  JDWRef AS JDWRef2,

                  TEXT (PURGECHAR(TEMP,'ABCD')) AS TEMP2

                  RESIDENT Remitt

                  order by RN;

                   

                  drop field JDWRef;

                   

                  JDWRefNum: 

                  Load

                  RN,

                  TEMP2,

                  IF (len (JDWRef2) >0, JDWRef2 , PEEK (TEMP2,-1)) as JDWRef

                  RESIDENT TEMP;

                   

                  //DROP TABLES AND FIELDS

                  DROP table TEMP;

                  //DROP FIELD TEMP2;

                  DROP FIELD TEMP;

                   

                  exit script;