topic Re: How to execute incremental load in QlikView

How to execute incremental load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am sharing the script. Please can anyone help me executing the incremental load. What are the steps to be followed for the same?

Script is attached here.

Firstly i have to store incremental load in qvd

then again once qvd is created again i have to load the qvd and run the script?

Please confirm ?

Re: How to execute incremental load

Anil_Babu_Samineni — Mon, 04 Dec 2017 05:20:14 GMT

How you want to use Increment Load? Normally, It can uses for DML operations to over ridden loading buffer elapse time in Qlik. Can you estimate where we are?

Re: How to execute incremental load

Anonymous — Mon, 04 Dec 2017 05:31:41 GMT

I want to use incremental load for all those fields which have been updated pr changed. So i need steps to execute the same like

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 ;

Once the above line is executed and qvd is created then are we suppose to execute the
created pOs.icremental? like below?

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

Please help?

Re: How to execute incremental load

Anil_Babu_Samineni — Mon, 04 Dec 2017 05:44:38 GMT

Can you explain what you want to implement using Increment Load. From this data you may get some input after that are you going to work with Inc. Load? What it does?

Re: How to execute incremental load

Anonymous — Mon, 04 Dec 2017 05:46:53 GMT

Yes I have to execute the script and get the data after which I have to only execute the incremental load

so How to do that?is this the way to execute the incremental load

Re: How to execute incremental load

Anil_Babu_Samineni — Mon, 04 Dec 2017 05:57:11 GMT

try this?

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

SQL SELECT *

FROM ETPEAS.dbo.SalesReturnTrn

WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

STORE Increment_Returns into [Path\FileName.Qvd (qvd)];

Drop Table Increment_Returns;

Incr_QVD:

Load * From [Path\FileName.qvd];

Max:

load max(Sold_Date) as max_Sold_Date

resident Incr_QVD;

Let vMax_Date = peek('max_Sold_Date',0,'Max')

Drop table Max;

Transaction:

join(Incr_QVD)

Load * From FileName.qvd where Sold_Date >'$(vMax_Date)';

Re: How to execute incremental load

Anonymous — Mon, 04 Dec 2017 06:53:57 GMT

This is a join which is to be executed so to execute joins firstly i have to load both the tables ?

Like first I have to load table sap_link and the warehouse?

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

Re: How to execute incremental load

Anil_Babu_Samineni — Mon, 04 Dec 2017 07:07:12 GMT

Perhaps this?

Increment_Sales:

Load *,AutoNumber(Field1 & '|' & Field2 & '|' & Field3) as Key; // These are the common fields from both tables

Load * ....;

Concatenate (Increment_Sales)

Increment_Returns:

Load *,AutoNumber(Field1 & '|' & Field2 & '|' & Field3) as Key; // These are the common fields from both tables

Load * ....;

LinkTable:

Load Key, Field1, Field2 From Increment_Sales;

concatenate (LinkTable)

Load Key, Field1, Field2 From Increment_Returns;

Drop Fields Field1, Field2 From Increment_Sales;

Drop Fields Field1, Field2 From Increment_Returns;

STORE LinkTable into [Path\FileName.Qvd (qvd)];

Drop Table LinkTable;

Incr_QVD:

Load * From [Path\FileName.qvd];

Max:

load max(Sold_Date) as max_Sold_Date

resident Incr_QVD;

Let vMax_Date = peek('max_Sold_Date',0,'Max')

Drop table Max;

Transaction:

join(Incr_QVD)

Load * From FileName.qvd where Sold_Date >'$(vMax_Date)';

Re: How to execute incremental load

Anonymous — Mon, 04 Dec 2017 07:12:08 GMT

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;

This is my incremental load tab script. Now can you please help me in rsolving the issue

Re: How to execute incremental load

Anil_Babu_Samineni — Mon, 04 Dec 2017 07:14:21 GMT

Instead sharing whole project script, Try to conflict with tiny code and to solve the issue. Because, If you are sharing entire script how come we know which data set of analyst data used for this project. Anyway, You can manipulate with my earlier response.

Re: How to execute incremental load

Anonymous — Mon, 04 Dec 2017 07:24:29 GMT

ok. let me try above code

Re: How to execute incremental load

Anonymous — Mon, 04 Dec 2017 07:33:03 GMT

sir other than quantity sold and inv no all feilds are common

so do i have to include all of the?

Re: How to execute incremental load

Anil_Babu_Samineni — Mon, 04 Dec 2017 08:06:36 GMT

Yes, Instead we can go simply Join and same last-last reply with Increment Load script existing. But, Your case is really open for all projects with first approach rather maintain Bridge Table again here.