problem in incremental load

[Anonymous]

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;

[Anonymous]

stalwar1‌ Please help sir?

[sunny_talwar]

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?

[Anonymous]

The script is not executing further

[sunny_talwar]

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/

[Anonymous]

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

[Anonymous]

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);

[sunny_talwar]

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

[Anonymous]

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);

[sunny_talwar]

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....