Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

problem in incremental load

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;

21 Replies
sunny_talwar

If you are pulling a lot of data... I am not sure what you expect? What do you think it should take and based on what?

Anonymous
Not applicable
Author

I am having a problem in executing a script. I am loading only one single table.Still getting an error

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

//LastUpdatedDate;

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

//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,

LastUpdatedDate

FROM

(qvd);

Increment_Sales_max_date:

LOAD max(LastUpdatedDate) as max_date

Resident Increment_Sales_qvd;

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

DROP Table Increment_Sales_qvd;

////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 LastUpdatedDate > $(vsmaxdate);

concatenate(Increment_Sales_icr)

Increment_Sales:

LOAD  GoodsSender,

     InvoiceNumber,

     CreateDate,

     ItemNumber,

     InvoiceQuantity,

     SalesPrice,

     LocalAmount,

     VatAmount,

     Walkins,

LastUpdatedDate

FROM

(qvd);

store Increment_Sales_icr into C:\Users\dmsadmin\Desktop\QlikView_UCB_Report\QVD\extract_qvdIncrement_Sales_icr.qvd;

drop table Increment_Sales_qvd;

why this is not executing??Paint1.png