Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robert99
Specialist III
Specialist III

Loading a PDF text file with a minus after a number

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

;

1 Solution

Accepted Solutions
robert99
Specialist III
Specialist III
Author

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;

View solution in original post

5 Replies
maximiliano_vel
Partner - Creator III
Partner - Creator III

Perhaps, you could post some sample data?

Rgds

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

sinanozdemir
Specialist III
Specialist III

Maybe something like this:

Capture.PNG

The minus sign will be in front of the number:

Capture.PNG

Hope this helps.

robert99
Specialist III
Specialist III
Author

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

robert99
Specialist III
Specialist III
Author

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;