topic Re: problem in incremental load in QlikView

problem in incremental load

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

I have a query regarding executing a script of incremental load. when 1 am executing the script its taking time to execute a code.Following is the code :-

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;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_Sales:

////-------- Start Multiple Select Statements ------

//LOAD

//GoodsSender,

// InvoiceNumber,

// CreateDate,

// ItemNumber,

// InvoiceQuantity,

// SalesPrice,

// LocalAmount,

// VatAmount,

// Walkins;

//SQL SELECT *

//FROM ETPEASV55.dbo."VW_CashOrderTRN1"

////WHERE InvoiceType = '31' and (CreateDate >= 20150101 and CreateDate <= 20161231) ;

////WHERE InvoiceType = '31' and (CreateDate >= 20150101 and CreateDate <= '$(currentdate)') ;

//WHERE InvoiceType = '31' and CreateDate <= convert(nvarchar(8),getdate()-1,112);

////and GoodsSender is not null ;

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

//drop table Increment_Sales;

Increment_Sales_qvd:

LOAD GoodsSender,

InvoiceNumber,

CreateDate,

ItemNumber,

InvoiceQuantity,

SalesPrice,

LocalAmount,

VatAmount,

Walkins

FROM

(qvd);

Increment_Sales_max_date:

LOAD max(CreateDate) as max_date

Resident Increment_Sales_qvd;

LET vsmaxdate=Peek('max_date',0,'Increment_Sales_max_date');

//DROP Table Increment_Sales_qvd;

concatenate

////Incremental Load only for Insert and Update

Increment_Sales_icr:

LOAD

GoodsSender,

InvoiceNumber,

CreateDate,

ItemNumber,

InvoiceQuantity,

SalesPrice,

LocalAmount,

VatAmount,

Walkins;

SQL SELECT *

FROM ETPEASV55.dbo."VW_CashOrderTRN1"

////WHERE InvoiceType = '31' and (CreateDate >= 20150101 and CreateDate <= 20161231) ;

WHERE InvoiceType = '31' and CreateDate <= convert(nvarchar(8),getdate()-1,112) and CreateDate > $(vsmaxdate);

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

drop table Increment_Sales_qvd;

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 11:42:30 GMT

stalwar1‌ Please help sir?

Re: problem in incremental load

sunny_talwar — Mon, 21 May 2018 11:55:03 GMT

I have a query regarding executing a script of incremental load. when 1 am executing the script its taking time to execute a code.

Taking time to execute? How much time? other times of the day it doesn't take that long?

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 12:08:00 GMT

The script is not executing further

Re: problem in incremental load

sunny_talwar — Mon, 21 May 2018 12:09:43 GMT

See if you can use another method to find max date using the method provided here by Rob

http://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 12:11:57 GMT

after 6 min second line got executed.Now again the further lines are not getting executed.

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 12:15:11 GMT

Following below table is causing problem.This is taking lot of time in execution. Please help?

concatenate

////Incremental Load only for Insert and Update

Increment_Sales_icr:

LOAD

GoodsSender,

InvoiceNumber,

CreateDate,

ItemNumber,

InvoiceQuantity,

SalesPrice,

LocalAmount,

VatAmount,

Walkins;

SQL SELECT *

FROM ETPEASV55.dbo."VW_CashOrderTRN1"

////WHERE InvoiceType = '31' and (CreateDate >= 20150101 and CreateDate <= 20161231) ;

WHERE InvoiceType = '31' and CreateDate <= convert(nvarchar(8),getdate()-1,112) and CreateDate > $(vsmaxdate);

Re: problem in incremental load

sunny_talwar — Mon, 21 May 2018 12:18:08 GMT

It all depends on how much data you are pulling and how much RAM do you have... if you are pulling a lot of data and you don't have enough resources, I don't really think you can do much about it

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 12:24:57 GMT

I am pulling data from 2015 till date.

is this condition correct to apply on database?

WHERE InvoiceType = '31' and CreateDate <= convert(nvarchar(8),getdate()-1,112) and CreateDate > $(vsmaxdate);

Re: problem in incremental load

sunny_talwar — Mon, 21 May 2018 12:27:25 GMT

I am not so sure about the syntax as this might be the data base thing... a database guy might be able to help you better with that... but the condition itself doesn't seem wrong....

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 12:29:42 GMT

thanks

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 16:46:12 GMT

This is the following error i am getting stalwar1‌

Re: problem in incremental load

sunny_talwar — Mon, 21 May 2018 16:55:22 GMT

Something wrong with the variable may be?

Add trace to check what value does the variable have

Increment_Sales_max_date:

LOAD max(CreateDate) as max_date

Resident Increment_Sales_qvd;

LET vsmaxdate=Peek('max_date',0,'Increment_Sales_max_date');

TRACE $(vsmaxdate);

EXIT SCRIPT;

Re: problem in incremental load

Anonymous — Mon, 21 May 2018 17:09:17 GMT

I am getting this value

Re: problem in incremental load

sunny_talwar — Tue, 22 May 2018 01:19:37 GMT

May be try with single quotes around the variable

CreateDate > '$(vsmaxdate)';

Re: problem in incremental load

Anil_Babu_Samineni — Tue, 22 May 2018 02:47:17 GMT

Probably, doing some Qlik functionality instead DB concepts for picking / storing the max value. It will help you in future for best practice. And, Will you able to tell how much size data hold and RAM size?

Re: problem in incremental load

Anonymous — Tue, 22 May 2018 03:21:48 GMT

Sir Its working but taking more than 8 hours to execute the script.

Re: problem in incremental load

Anonymous — Tue, 22 May 2018 03:24:11 GMT

Ram size is 8GB

Re: problem in incremental load

Anil_Babu_Samineni — Tue, 22 May 2018 03:40:02 GMT

Sunny already point - Could be data size is huge. Let me share my work environment.

Data size -- 20 Billion Rows (5 GB)

RAM - 4 GB

File size shouldn't be less than of RAM memory. So, It takes for me to reload around 6.5 hours. And, After that we have requested to windows team who care of configuration. and they extend to 8 GB. Now, still it is taking 4 hours. For that we need to do some optimization for script to increase performance.

Re: problem in incremental load

Anonymous — Tue, 22 May 2018 04:22:09 GMT

so how to do that?