Incremental Load

[smilingjohn]

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....

[#2]

Hi John,

I am not able to open this file.

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

[smilingjohn]

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;

[puttemans]

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.

[mukesh24]

hi,

Please check following link, may be useful for you

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

[puttemans]

I found out. Newest version here.

[smilingjohn]

HI Johan thanku you so much

I saw that u made some chages ,

Just i wanted to know is this now Insert Only ?

[puttemans · Accepted solution]

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.

[smilingjohn]

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 ?

[puttemans]

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.