topic Re: Incremental load script in QlikView

Incremental load script

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Incremental load script

Kushal_Chawda — Mon, 17 Jul 2017 14:02:13 GMT

Which is the Updated Date in table?

Re: Incremental load script

effinty2112 — Mon, 17 Jul 2017 14:11:41 GMT

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

Re: Incremental load script

madhuqliklondon — Mon, 17 Jul 2017 14:56:22 GMT

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.

Re: Incremental load script

Kushal_Chawda — Tue, 18 Jul 2017 10:26:42 GMT

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;

Re: Incremental load script

madhuqliklondon — Thu, 20 Jul 2017 09:48:22 GMT

Hi Andrew,

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

Re: Incremental load script

madhuqliklondon — Thu, 20 Jul 2017 09:49:43 GMT

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

Re: Incremental load script

effinty2112 — Thu, 20 Jul 2017 09:56:26 GMT

Hi Madhu,

try:

WHERE [idDate] > '$(vLastDate )'

Good Luck

Andrew

Re: Incremental load script

Kushal_Chawda — Thu, 20 Jul 2017 10:11:51 GMT

which database you are using to pull the data?

Re: Incremental load script

jmvilaplanap — Thu, 20 Jul 2017 10:16:40 GMT

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

Re: Incremental load script

madhuqliklondon — Thu, 20 Jul 2017 10:20:50 GMT

Sqlserver

Re: Incremental load script

Nagaraju_KCS — Thu, 20 Jul 2017 10:31:05 GMT

Hi Madhu,

Before doing Incremental load convert date to 111 format.

Date should be YYYY/MM/DD, application will compare fastly

SET vQvdFile='TableName.QVD';

SET vTableName='TableName';

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN

maxdateTab:

LOAD Max(Date(YourDate,'YYYY/MM/DD')) as maxdate

FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = 'where Convert(varchar(10), ("YourDate"),111) >' & Chr(39) & peek('maxdate') & Chr(39);

DROP Table maxdateTab;

ELSE

LET vIncrementalExpression = '';

END IF

$(vTableName):

LOAD

[idDate]

,[idArticle]

,[idStore]

,[idCustomer]

,[Qty]

,[NetAmount]

,[BrutAmount]

,[MarginAmt]

,[Rank_ArtCust];

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

$(vIncrementalExpression);

IF $(vQvdExists) THEN

CONCATENATE ($(vTableName))

LOAD * FROM $(vQvdFile) (qvd);

End IF

STORE $(vTableName) INTO TableName.QVD (QVD);

DROP Table $(vTableName);

Re: Incremental load script

Kushal_Chawda — Thu, 20 Jul 2017 11:23:29 GMT

then this should work, please check the syntax

Re: Incremental load script

madhuqliklondon — Thu, 20 Jul 2017 11:35:53 GMT

Sure I will check. Thank you for your help.

Re: Incremental load script

madhuqliklondon — Thu, 20 Jul 2017 11:36:09 GMT

Thank you.