Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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?
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??