Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have to execute incremental load but I want to know how incremental load works?
It overwrites the existing records?
or it adds all new records in the files?
Maybe this could help
https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/
It will load only new records based on the key field and then u need to concatenate it with old data.
Suppose u have \
ID,Salary
1,200
2,300
Now you have loaded the data.
If some more IDs are coming, suppose, ID =3,4,5.
then u can use incremental load to load these IDs data.
It will load only data for these Ids=3,4,5 and then u need to concatenate Id=1,2 data into it.
It is a kind of best practice if u have large amount of data and u need to refresh QVD daily based on date.
Thanks for sharing the link. Following is my script and I want to execute insert and update in incremental load.
I have following tables connected with sql
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=ETPEAS;Data Source=192.168.1.70;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](XPassword is XEDJTZFMLB);
Increment_Sales:
Concatenate(POS_Master_final)
Load
Warehouse as ETPCode,
InvoiceNumber as [inv no],
Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
InvoiceQuantity as quantity_sold,
SalesPrice as [sales price],
LocalAmount as [inv value],
if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
VatAmount as [tax amount];
SQL SELECT *
FROM ETPEAS.dbo.CashOrderTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;
Increment_Returns:
Concatenate(POS_Master_final)
LOAD
Warehouse as ETPCode,
SalesReturnNumber as [return no],
Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
replace(ReturnItemNumber,' ','') as itemnumber,
left(ReturnItemNumber,3) as [Season Code],
ReturnQuantity as quantity_returns,
SalesPrice as [sales price],
-LocalAmount as [inv value],
if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket];
SQL SELECT *
FROM ETPEAS.dbo.SalesReturnTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;
//store POS_Master_final into
Incremental_Non_Etp_Sales:
Concatenate(POS_Master_final)
Load
ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,
InvoiceNumber as [inv no],
Date#(SalesDate, 'YYYYMMDD') as Sold_Date,
month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
//'N_ETP SALE' as SaleType,
SaleQuantity as quantity_sold,
NetValue as [inv value],
MRPValue as [sales price],
if ( NetValue <500, '<500', if ( NetValue <800, '500-799',if ( NetValue <1200, '800-1199',if ( NetValue <1600,'1200-1599',if ( NetValue <2500,'1600-2499',if ( NetValue <3500,'2500-3499',if ( NetValue <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
TaxAmount as [tax amount];
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId = WH.Warehouse
where SD.Doc_Type = 'ZIKE' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;
Incremental_Non_Etp_Returns:
Concatenate(POS_Master_final)
Load
ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,
InvoiceNumber as [return no],
Date#(SalesDate, 'YYYYMMDD') as Sold_Date,
month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
//'N_ETP SALE' as SaleType,
SaleQuantity as quantity_returns,
-NetValue as [inv value],
MRPValue as [sales price],
if ( NetValue <500, '<500', if ( NetValue <800, '500-799',if ( NetValue <1200, '800-1199',if ( NetValue <1600,'1200-1599',if ( NetValue <2500,'1600-2499',if ( NetValue <3500,'2500-3499',if ( NetValue <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
-TaxAmount as [tax amount];
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'ZIKR' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;
Increment_Receiving_temp1:
LOAD
TOWarehouse as ETPCode,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
Date#(CurrTransactionDate, 'YYYYMMDD') as Received_Date,
ReceivingNumber as [rec number],
ReceivedQuantity as quantity_received;
SQL SELECT *
FROM ETPEAS.dbo.DOLine
WHERE(CurrTransactionDate >= 20130101 and CurrTransactionDate <= '$(currentdate)') ;
Incremental_Receiving_Non_Etp:
Concatenate (Increment_Receiving_temp1)
Load
ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,
Date#(SalesDate, 'YYYYMMDD') as Received_Date,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
InvoiceNumber as [rec number],
SaleQuantity as quantity_received
;
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'GR' and SD.SalesDate <= '$(currentdate)' and SD.SalesDate >= 20130101;
Increment_Receiving_temp2:
Concatenate (Increment_Receiving_temp1)
LOAD
Warehouse as ETPCode,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
Date#(CurrTransactionDate, 'YYYYMMDD') as Received_Date,
-ReceivedQuantity as quantity_received;
SQL SELECT *
FROM ETPEAS.dbo.DOLine
WHERE(CurrTransactionDate >= 20130101 and CurrTransactionDate <= '$(currentdate)') ;
Incremental_Receiving_Non_Etp_2:
Concatenate (Increment_Receiving_temp1)
Load
ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,
Date#(SalesDate, 'YYYYMMDD') as Received_Date,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
InvoiceNumber as [rec number],
-SaleQuantity as quantity_received
;
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'STN' and SD.SalesDate <= '$(currentdate)' and SD.SalesDate >= 20130101;
Increment_Receiving_final:
left keep (Temp)
load
ETPCode,
itemnumber,
[Season Code],
quantity_received,
Received_Date
Resident Increment_Receiving_temp1;
drop table Increment_Receiving_temp1;
Increment_Receiving_data:
Concatenate(POS_Master_final)
load *
Resident Increment_Receiving_final;
drop table Increment_Receiving_final;
drop table Temp;
How to execute incremental load?
//POS_Master:
//Load
//ETPCode,
//[inv no],
//Sold_Date,
//Sold_Month,
//Sold_Year,
//itemnumber,
//[Season Code],
//quantity_sold,
//[sales price],
//[inv value],
//[tax amount],
//[Inv val Bucket],
//[return no],
//quantity_returns
////quantity_received,
////First_Received_Date
//FROM
//D:\Data\Final QVD\Final\POS_Master_incremental.qvd (qvd)
//where Sold_Date <= MakeDate(2015,02,28) and NOT WildMatch(ETPCode,'*ETP*','N*N','N*');
/*
Last_Updated_Date:
load
max(Sold_Date) as Max_Date_Sales
RESIDENT POS_Master;
Let Last_updated_Sales_date = DATE(peek('Max_Date_Sales',0,'Last_updated_Sales_date')+1,'YYYYMMDD');
*/
/*
is this the way?
I am refreshing my QVD where my data source is Oracle
TempTable:
LOAD MAX(LAST_UPDATE_DATE) AS LUD
FROM [Lib://QVD/<QVDNAME>.qvd] (qvd);
LET MAX_LUD = Timestamp(peek('LUD')) ;
trace ($(MAX_LUD));
[oe_order_headers_all]:
SQL SELECT HEADER_ID,
LAST_UPDATE_DATE AS OOHA_LAST_UPDATE_DATE,
<MORE COLUMNS>
FROM <TABLE NAME>
WHERE TO_DATE(TO_CHAR(LAST_UPDATE_DATE,'mm/dd/yyyy hh:mi:ss AM'),'mm/dd/yyyy hh:mi:ss AM') > TO_DATE('$(MAX_LUD)', 'mm/dd/yyyy hh:mi:ss AM');
Concatenate
Load *
FROM [Lib://QVD/<QVDNAME>.qvd] (qvd)
WHERE NOT Exists(HEADER_ID);
STORE oe_order_headers_all INTO [Lib://QVD/<QVDNAME>.qvd] (qvd);
Drop Table oe_order_headers_all;
Drop Table TempTable;
Hi By using Incremental load, we can achieve different functionality based on requirement.
please refer following link to understand
https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/
.
so this code seems ok now, what problem are u getting in executing the script?
For getting the first value, u need to put 0 in the peek statement.
LET MAX_LUD = Timestamp(peek('LUD',0,'TempTable')) ;
NO Problem. It is executing perfectly. Adding new records and updating existing one.
If answer is correct please mark as answered.
The script will return only one row as max function is used and 0 in peek will return first row.