topic Re: Incremental load not functioning correctly in QlikView

Incremental load not functioning correctly

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

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;

Re: Incremental load not functioning correctly

passionate — Mon, 25 Jun 2018 15:08:43 GMT

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

Re: Incremental load not functioning correctly

sakshikaul — Tue, 26 Jun 2018 03:29:58 GMT

how ?

Re: Incremental load not functioning correctly

jonathandienst — Tue, 26 Jun 2018 05:59:54 GMT

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.

Re: Incremental load not functioning correctly

qlikviewwizard — Tue, 26 Jun 2018 06:08:26 GMT

Hi,

Please check this. Incremental Load in Qlikview - Sources

Re: Incremental load not functioning correctly

BalaBhaskar_Qlik — Sun, 05 Aug 2018 12:09:09 GMT

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