Incremental load not functioning correctly

[sakshikaul]

Hi

I want to execute incremental load in which i want to pass condition as Receipt > $(vincr_rec_temp2)-20 but here -20 is not working correctle. How to handle that?

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ETPEASV55;Data Source=192.168.1.108;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=QLIKVIEW;Use Encryption for Data=False;Tag with column collation when possible=False];

Increment_Receiving_temp2:

LOAD distinct

    SAPCode,

    ItemNumber,

    Receipt,

    ReceivingNumber,

    ReceivedQuantity;

SQL SELECT *

FROM ETPEASV55.dbo."VW_DOLineReceiving"

WHERE(CurrTransactionDate >= 20160101 and CurrTransactionDate <= '$(currentdate)') ;

store Increment_Receiving_temp2 into C:\Users\dmsadmin\Desktop\QlikView_UCB_Report\QVD\extract_qvd\Increment_Receiving_temp2.qvd;

drop table Increment_Receiving_temp2;

Increment_Receiving_temp2_qvd:

LOAD SAPCode,

     ItemNumber,

     Receipt,

     ReceivingNumber,

     ReceivedQuantity

FROM

(qvd);

Increment_Receiving_temp2_max_date:

LOAD max(  Receipt) as max6_date

Resident Increment_Receiving_temp2_qvd;

LET vincr_rec_temp2=Peek('max6_date',0,'Increment_Receiving_temp2_max_date');

drop table Increment_Receiving_temp2_qvd;

Increment_Receiving_temp2_incr:

LOAD SAPCode,

     ItemNumber,

     Receipt,

     ReceivingNumber,

     ReceivedQuantity;

SQL SELECT *

FROM ETPEASV55.dbo.VW_DOLineReceiving

//WHERE(CurrTransactionDate >= 20150101 and CurrTransactionDate <= '$(currentdate)') ;

where   Receipt >$(vincr_rec_temp2)-20;

concatenate(Increment_Receiving_temp2_incr)

Increment_Receiving_temp2:

LOAD SAPCode,

     ItemNumber,

     Receipt,

     ReceivingNumber,

     ReceivedQuantity

FROM

(qvd);

//Incremental Load for Insert, Update and Delete

inner join(Increment_Receiving_temp2_incr)

Increment_Receiving_temp2_incr_ID:

LOAD distinct

SAPCode,

     ItemNumber,

     Receipt,

     ReceivingNumber,

     ReceivedQuantity;

SQL SELECT *

FROM ETPEASV55.dbo.VW_DOLineReceiving;

store Increment_Receiving_temp2_incr into C:\Users\dmsadmin\Desktop\QlikView_UCB_Report\QVD\extract_qvd\Increment_Receiving_temp2_incr.qvd;

drop table Increment_Receiving_temp2_incr;

[passionate]

You cannot substract 20 from date directly you should first convert date to number and then subtract.

[sakshikaul]

how ?

[jonathandienst]

Do the arithmetic in Qlik and format the result correctly:

LET vincr_rec_temp2 = Date(Num(Peek('max6_date',0,'Increment_Receiving_temp2_max_date')) - 20, 'YYYYMMDD');

Then the WHERE clause should be

Receipt > $(vincr_rec_temp2);

I had to make assumptions about how dates work in your database and the Receipt field because you have not specified data type of Receipt or the type of database you are using. You may have to adapt the above.

[qlikviewwizard]

Hi,

Please check this. Incremental Load in Qlikview - Sources

[balabhaskarqlik]

Is your issue resolved, post the solution. It'll be useful to others.