Re: What is the functionality of incremental load ... - Qlik Community

Anonymous · ‎2017-12-05

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?

luismadriz · ‎2017-12-05

Maybe this could help

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

shiveshsingh · ‎2017-12-05

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.

Anonymous · ‎2017-12-05

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 ;          //This is an incremental file stored in qvd)

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?

zahidrahim_ocp · ‎2017-12-05

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,

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;

prashantsanchet · ‎2017-12-05

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/

.

shiveshsingh · ‎2017-12-05

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

zahidrahim_ocp · ‎2017-12-05

NO Problem. It is executing perfectly. Adding new records and updating existing one.

If answer is correct please mark as answered.

zahidrahim_ocp · ‎2017-12-05

The script will return only one row as max function is used and 0 in peek will return first row.

What is the functionality of incremental load ?