Incremental load script

[madhuqliklondon]

Hi Friends,

I have created .qvd file and done initial load by below script

Amount:

Load

  [idDate]

,[idArticle]

,[idStore]

,[idCustomer]

,[Qty]

,[NetAmount]

,[BrutAmount]

,[MarginAmt]

,[Rank_ArtCust];

sql select * from [wpreplic].[dbo].[WHISTOMVT];

store Amount into C:\Users\madhu\Desktop\Qvd\Amount.qvd(qvd);

DROP Table Amount;

How can write incremental load script for this , the source(sql) is getting updated once in a day.

Thank you

[Kushal_Chawda]

Which is the Updated Date in table?

[effinty2112]

Hi Madhu,

Maybe something like:

Load * From C:\Users\madhu\Desktop\Qvd\Amount.qvd(qvd);

tblMaxDate:

LOAD 

Floor(Max(Fieldvalue('idDate',RecNo()))) as LastDate

AUTOGENERATE FieldValueCount('idDate');

Let vLastDate = Date(Peek('tblMaxDate',0,'LastDate'));

DROP Table tblMaxDate;

Concatenate(Amount) // not necessary but explanatory

Load

  [idDate]

,[idArticle]

,[idStore]

,[idCustomer]

,[Qty]

,[NetAmount]

,[BrutAmount]

,[MarginAmt]

,[Rank_ArtCust];

sql select * from [wpreplic].[dbo].[WHISTOMVT]

WHERE [idDate] > $(vLastDate );

store Amount into C:\Users\madhu\Desktop\Qvd\Amount.qvd(qvd);

DROP Table Amount;

Cheers

Andrew

[madhuqliklondon]

IdDate is like 20170714 in sql when I convert that into Date(IdDate) the year coming up with 5 digits ex: 57068 and months and dates are not matching as well.. changed at the script level as well year(IdDate)  as Year still its is the same. How do I get Normal 4 digit year.

@Kushal  (IdDate) is updates date ..

@ Andrew ..thanks I will try that.

[Kushal_Chawda]

MaxDate:

LOAD date(max([idDate]),'DD/MM/YYYY') as MaxDate

FROM 

C:\Users\madhu\Desktop\Qvd\Amount.qvd(qvd);

let vMaxDate = peek('MaxDate',0,'MaxDate');

Amount:

Load

  [idDate]

,[idArticle]

,[idStore]

,[idCustomer]

,[Qty]

,[NetAmount]

,[BrutAmount]

,[MarginAmt]

,[Rank_ArtCust];

sql select * from [wpreplic].[dbo].[WHISTOMVT]

where to_date(to_char(idDate,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date('$(vMaxDate)','DD/MM/YYYY') ;

concatenate(Amount)

LOAD *

FROM 

C:\Users\madhu\Desktop\Qvd\Amount.qvd(qvd);

store Amount into C:\Users\madhu\Desktop\Qvd\Amount.qvd(qvd);

DROP Table Amount;

[madhuqliklondon]

Hi Andrew,

It's giving error at this point   WHERE [idDate] > $(vLastDate );  any idea??

[madhuqliklondon]

Hi Kushal , It is not recognizing this part

----where to_date(to_char(idDate,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date('$(vMaxDate)','DD/MM/YYYY') ;

It says it is not in built function. Thank you 

[effinty2112]

Hi Madhu,

try:

WHERE [idDate] > '$(vLastDate )'

Good Luck

Andrew

[Kushal_Chawda]

which database you are using to pull the data?

[jmvilaplanap]

Hi,

Depending your incremental type (only add, replace, update, etc...) you have here a great post explaining how to do it

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

Regards