topic Re: incremental tables script explain. in QlikView

incremental tables script explain.

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

hi can any one please explain me the difference between two scripts :-

first script:-

"NonTest":

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd(qvd);

catenate (NonTest)

SQL select *

FROM "62_DW".dbo."t_test"

where Date>$(vMaxDate);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

2nd script :-

"NonTest":

SQL select *

FROM "62_DW".dbo."t_test"

where Date>$(vMaxDate);

Temp:

load max (Date) as MaxDate

resident NonTest;

let vMaxDate=peek('MaxDate');

drop table Temp;

Concatenate (NonTest)

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd(qvd);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

which script will work for incremental load , please tell me.

Re: incremental tables script explain.

dplr-rn — Mon, 16 Sep 2019 17:06:32 GMT

from a quick look; your first one looks right for insert only incremental load apart from it missing code to populate variable vMaxDate.

use below link for a good tutorial on incremental load
https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: incremental tables script explain.

riishabhz — Mon, 16 Sep 2019 17:35:27 GMT

Hi sir, can you please share your script of incremental sql, i am in need
sir, please help me

Re: incremental tables script explain.

dplr-rn — Mon, 16 Sep 2019 17:52:14 GMT

Look at #1 in that link that will help you.

Re: incremental tables script explain.

riishabhz — Mon, 16 Sep 2019 17:54:27 GMT

I tried that link sir, but data is getting double dont know why.

Re: incremental tables script explain.

dplr-rn — Mon, 16 Sep 2019 17:56:07 GMT

share your script.

Re: incremental tables script explain.

riishabhz — Mon, 16 Sep 2019 18:01:27 GMT

My script :- data is getting double dont know why
max1:
LOAD max (SalesDate) as SalesDate
FROM
[..\VWCashOrderTRN_Qlik\VWCashOrderTRN_Qlik.qvd](qvd);

let date2=Date(peek('SalesDate',0,'max1'));

drop table max1;

final1:
LOAD NCode,
PartnerCode,
Channel,
ETPCode,
SalesDate,
"Doc_Type",
BillNo,
CustomerNumber,
Season,
Style,
Color,
Size,
InvoiceQuantity,
ETPMRP,
RealisedSale,
TAXAmount,
TAXRate,
NETSale,
SalesPerson;
SQL SELECT *
FROM ETPEASV55.dbo."VWCashOrderTRN_Qlik" where SalesDate>'$(date2)';

Concatenate(final1)
LOAD NCode,
PartnerCode,
Channel,
ETPCode,
SalesDate,
Doc_Type,
BillNo,
CustomerNumber,
Season,
Style,
Color,
Size,
InvoiceQuantity,
ETPMRP,
RealisedSale,
TAXAmount,
TAXRate,
NETSale,
SalesPerson
FROM
[..\VWCashOrderTRN_Qlik\VWCashOrderTRN_Qlik.qvd]
(qvd);

Re: incremental tables script explain.

dplr-rn — Mon, 16 Sep 2019 18:32:42 GMT

almost certainly your where clause is not working correctly.
why do you have single quotes around the variable there?
FROM ETPEASV55.dbo."VWCashOrderTRN_Qlik" where SalesDate>'$(date2)';

the overall logic looks correct. you need to make sure the where clause executes correctly

Re: incremental tables script explain.

riishabhz — Mon, 16 Sep 2019 18:46:27 GMT

Ok, should i use this script or from analyticsvidya. Com which you gave me
to follow the incremental

Re: incremental tables script explain.

dplr-rn — Mon, 16 Sep 2019 18:48:06 GMT

use one from the link

Re: incremental tables script explain.

riishabhz — Mon, 16 Sep 2019 18:50:27 GMT

Ok sir i will update you in 12hrs

Re: incremental tables script explain.

Brett_Bleess — Thu, 26 Sep 2019 14:34:54 GMT

Not sure if you are still working on things or not, but I wanted to give you a couple of links just in case you had not found them:

https://community.qlik.com/t5/Qlik-Design-Blog/Overview-of-Qlik-Incremental-Loading/ba-p/1466780

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/QVD_Incremental.htm

Hopefully those may help if you are still struggling to understand things. Do not feel badly, this area is quite tricky to get a handle upon as well, just keep trying, and I am confident you will get it sorted out, and it will likely make more sense at that point too. Shout if you have more questions, hopefully one of us can try to come up with some further info for you in that case.

Regards,
Brett