topic Re: Incremental Load in QlikView

Incremental Load

smilingjohn — Mon, 09 Mar 2015 08:46:53 GMT

Hi All,

Iam attaching the qlikview file , in which i have implemented INSERT ONLY Incremental load

Can some one plz confirm me that this is correct or not ?????????

Plz need ur guidance on this

Thanks in advance....

Re: Incremental Load

Mon, 09 Mar 2015 08:59:40 GMT

Hi John,

I am not able to open this file.

But here you go with the attachment of Incremental load with all steps...

Re: Incremental Load

smilingjohn — Mon, 09 Mar 2015 09:09:18 GMT

Thanks for the reply shshyamv908 .

Its really confusing me , i cant understand it , i dont know why u r niot able to open it

PLz see below i have copied the code from script :

// INSERT ONLY INCREMENTAL LOAD

SET VSaveQVD = 'D:\Incremental Data\QVD\';

//vExectime=today();

vReloaddatetime='';

if (FileSize('D:\Incremental Data\QVD\Billing.qvd'))>0 then

Loadtime:

LOAD

max(Reloaddatetime) As Reloaddatetime

FROM

(qvd);

vReloaddatetime=peek('Reloaddatetime',0,'Loadtime')

drop table Loadtime;

else

vReloaddatetime='01/01/2010';

ENDIF

set scripterror=0;

Billing:

Load *,

LocalTime() As Reloaddatetime;

LOAD [Account Assign.],

[Sales Office],

[Sales Office Desc],

[Distri channel],

Division,

[Divi Desc],

[Invoice Date] as Date,

TOP,

[Sales order date],

Material,

[Material desc],

[Billed qty],

[Sales Unit],

[Exchange Rate],

Currency,

Tonnage,

[Sales amt],

[Cash Discount],

[Total sales value] ,

VAT,

[Additional VAT],

[Social security cess],

CST,

Segment,

Usage,

[WBS Element],

[MM Matrial Grp],

[MM Material Grp Desc]

FROM

(biff, embedded labels, table is Sheet1$)

where [Invoice Date]>'$(vReloaddatetime)';

if(FileSize('D:\Incremental Data\QVD\Billing.qvd'))>0then

Concatenate('Billing')

load *

from D:\Incremental Data\QVD\Billing.qvd(qvd)

where Date<'$(vReloaddatetime)';

ENDIF

store * from Billing into D:\Incremental Data\QVD\Billing.qvd;

Re: Incremental Load

puttemans — Mon, 09 Mar 2015 09:30:19 GMT

Hello John,

I tried to rework it already as good as possible. I'm a bit worried about line 62, as the where clause is not getting the right colour. Please have a look.

Re: Incremental Load

mukesh24 — Mon, 09 Mar 2015 09:31:39 GMT

hi,

Please check following link, may be useful for you

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: Incremental Load

puttemans — Mon, 09 Mar 2015 09:34:07 GMT

I found out. Newest version here.

Re: Incremental Load

smilingjohn — Mon, 09 Mar 2015 09:59:46 GMT

HI Johan thanku you so much

I saw that u made some chages ,

Just i wanted to know is this now Insert Only ?

Re: Incremental Load

puttemans — Mon, 09 Mar 2015 10:10:39 GMT

Hi John,

It should select lines that are from a different date than what you already have in the 'Billing' file, and add them to the billing file. So yes, insert only.

Please pay attention, this script implies you'd only take lines from e.g. 8/3/2015 in your 'Billing_Testing' file if you re-load today before noon. Else, there may be lines from e.g. this afternoon with a date that will be already in your 'Billing' file tomorrow that won't be picked up tomorrow.

Re: Incremental Load

smilingjohn — Mon, 09 Mar 2015 10:31:23 GMT

HI johan

After Implementing your code i loaded the script and iam attaching the screen shot . in my excel data i added one more row .before that it had only 8 lines but now there are totally 9 lines .

if its insert only it should show me lines fetched 1

but why it is showing billing qvd optimized 9 lines fetched ?

Re: Incremental Load

puttemans — Mon, 09 Mar 2015 10:38:23 GMT

Hi john,

Billing to Loadtime : here, 1 line is taken, which is the maximum date from the billing file.

Sheet1$ to Billing : 1 line is fetched. This should be the line you added. This line is selected based upon the date from the Sheet1$.

Billing to Billing : in total 9 lines fetched. The script will add the existing lines from the Billing file (8 in this case), and replace the old Billing file.

Hope this helps.