topic Re: Incremental load with two load statements in QlikView

Incremental load with two load statements

Thu, 22 Jan 2015 16:10:59 GMT

My initial load looks like below. Works just fine.

However when Im applying an incremental load approach, the second load does not work properly.

Pls see attached QV-file.

Any ideas why?

CSVLIST:
LOAD
     textbox22 as BANKNAME,
     IF(textbox18='EUR', RIGHT(Account_1,8),RIGHT(Account_1,7)) as ACC,
     textbox18 as CCY,
     Ref1 as REF,
     DATE(Val_Date, 'MM/DD/YYYY') as VD,
     IF(DC='DEBIT',Amount*-1,Amount*1) as AMOUNT,
     Filename() as FILENAME,
     IF(RIGHT(FILENAME(),3)='csv','CSV LIST') as SOURCE,
     IF(RIGHT(FILENAME(),3)='csv','New transaction') as [TRANSACTION STATUS],
IF(RIGHT(FILENAME(),3)='csv','YES') as [ILM RESPONSIBLE],
Closing_Balance_Date as [REPORT DATE]


FROM
$(vSourceData)*.csv
(txt, unicode, embedded labels, delimiter is ';', msq)
WHERE Account_1 <> '8840039250-500150' and Amount<>'' ;

Concatenate LOAD
     textbox22 as [EOD BANK NAME],
     IF(textbox18='EUR', RIGHT(Account_1,8),RIGHT(Account_1,7)) as [EOD ACC],
     textbox18 as [EOD CCY],
     Closing_Balance_Date as [EOD BALANCE DATE],
     IF(DC_Bal2='Debit',Closing_Balance *-1, Closing_Balance) as [EOD BALANCE],
     Filename() as [EOD FILENAME]

FROM
$(vSourceData)*.csv
(txt, unicode, embedded labels, delimiter is ';', msq)
WHERE Account_1 <> '8840039250-500150';

STORE CSVLIST into $(vSaveQVD)CSV.qvd(qvd);
DROP TABLE CSVLIST;

Re: Incremental load with two load statements

Fri, 23 Jan 2015 11:07:23 GMT

Anyone pls?

Re: Incremental load with two load statements

Peter_Cammaert — Fri, 23 Jan 2015 11:14:17 GMT

I don't get it (my bad). At first, you're reading entire csv files and storing them in a QVD. The next time around, you're doing just the same. Where is the incremental part, that starts from a previously created QVD and only adds the new lines from the csv's?

Re: Incremental load with two load statements

Peter_Cammaert — Fri, 23 Jan 2015 11:17:05 GMT

Sorry, wasn't looking at the script in your document.

You're saying that the incremental part doesn't work. Any idea where it fails, or what exactly it fails to do well? There are no source files included, so it's a bit difficult to test.

Re: Incremental load with two load statements

Peter_Cammaert — Fri, 23 Jan 2015 11:19:33 GMT

Just an idea: are you sure that on tab CSV - MAIN QVD LOAD the second load doesn't AUTOCONCATENATE? Try with the NOCONCATENATE prefix.

Re: Incremental load with two load statements

Peter_Cammaert — Fri, 23 Jan 2015 11:24:47 GMT

I think you'ld better specify the NOCONCATENATE prefix everywhere. In the end, you should have a table called Table2_CSV (it is never dropped). However it doesn't exist.

BTW you're reading the source QVD twice. I think it would be more efficient if you keep it in memory and add the incremental part before overwriting the QVD.