topic Re: Loading a minus after a number in App Development

Loading a PDF text file with a minus after a number

robert99 — Thu, 13 Aug 2015 21:49:42 GMT

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_vel — Fri, 14 Aug 2015 00:41:25 GMT

Perhaps, you could post some sample data?

Rgds

Re: Loading a minus after a number

sunny_talwar — Fri, 14 Aug 2015 01:01:46 GMT

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

sinanozdemir — Fri, 14 Aug 2015 01:16:01 GMT

Maybe something like this:

The minus sign will be in front of the number:

Hope this helps.

Re: Loading a minus after a number

robert99 — Fri, 14 Aug 2015 07:08:16 GMT

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

robert99 — Fri, 14 Aug 2015 17:02:16 GMT

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;