Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
;
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;
Perhaps, you could post some sample data?
Rgds
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
Maybe something like this:
The minus sign will be in front of the number:
Hope this helps.
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
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;