topic Re: how to execute incremental load? in QlikView

how to execute incremental load?

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

I want to execute incremental load. The script is attached below

Incremental (in script )

//store POS_Master_final into ;
this Pos_master is created from this above line

Pos_master(in scrip)(once this file has been created then again its loaded and the script is executed so please confirm is this a right way to do ?)

//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');
*/
/*
Store_Mapping:
Load
*
FROM
D:\Data\Final QVD\Final\Store_Mapping.qvd (qvd);
*/

Re: how to execute incremental load?

Anil_Babu_Samineni — Wed, 22 Nov 2017 06:00:21 GMT

What are you going to achieve?

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 06:25:19 GMT

I want to just check whether this is a right procedure to execute incremental load?

Re: how to execute incremental load?

Anil_Babu_Samineni — Wed, 22 Nov 2017 06:29:26 GMT

In that Store_Mapping table you need to restrict the data like

Store_Mapping:
Load
*
FROM
D:\Data\Final QVD\Final\Store_Mapping.qvd (qvd) Where Sold_Date >= $(Last_updated_Sales_date);

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 06:39:05 GMT

sold_date column is not present in this qvd

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 06:45:52 GMT

LOAD SAPCode,
ApplyMap('Store_SAP_Code',SAPCode,'') as [Store Name],
ETPCode,
UCB_Data_check,
[Dist Channel],
[Bill To Name],
[Ship To Name],
Tier,
[Bill To State],
Store_ranking,
[Sales Group],
[Ship to State],
Brand_06,
Brand_0Y,
[Condition group 4],
Country,
[Customer classific],
District,
Group,
[Interior Designer],
[No of Floors],
[No of Shop Window],
[Operating Cost],
Outlet_store,
PostalCode,
Region,
[Rent (2013 %)],
[Rent (2013 A)],
[Rent (2014 A)],
[Rent(2014 %)],
[Shop Opening Year],
[Shop Postion],
[Stock Room Surface],
[Store Area],
Street,
[Active flag],
City
FROM

(ooxml, embedded labels, table is Sheet1);

Re: how to execute incremental load?

Anil_Babu_Samineni — Wed, 22 Nov 2017 06:53:50 GMT

You can use Left Join to map each from Increment to Fact table, This case.

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 07:08:32 GMT

how to do that?

Re: how to execute incremental load?

Anil_Babu_Samineni — Wed, 22 Nov 2017 07:21:13 GMT

PFA

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 07:31:45 GMT

These are fact tables

Temp:
load ETPCode
RESIDENT Store_Mapping;

MRP_temp:
mapping LOAD itemnumber,
MRP_INR
Resident Material_Master;

SAPCODE_ETPCodeMapping:
Mapping load SAPCode,
ETPCode
Resident Store_Mapping;
This is Incremental load
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)') ;

Re: how to execute incremental load?

Anil_Babu_Samineni — Wed, 22 Nov 2017 08:45:18 GMT

When you say these are the fact tables, Then what does this is? POS_Master_final

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 09:35:55 GMT

Orders:
LOAD
[0SOLD_TO:0SOLD_TO] as SAPCode_Order,
[0MATERIAL:0MATERIAL]&[0AF_COLOR:0AF_COLOR]&[0AF_SIZE:0AF_SIZE] as itemnumber,
left([0MATERIAL:0MATERIAL],3) as [Season Code],
[0CONF_QTY:0CONF_QTY] as quantity_order,
[Z_MRP_INR:Z_MRP_INR] as Sales_price_order,
([Z_WSP_INR:Z_WSP_INR]/[0CONF_QTY:0CONF_QTY]) as WS_price
FROM

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

SAPCode_Orders:
Mapping LOAD
@2 as SAP_Trans_code,
@3 as SAPCode_Order
FROM

(txt, codepage is 1252, no labels, delimiter is '|', msq);

Material_Orders:
Mapping Load
@2&'N'&@3 as Material_Trans_code,
trim(@7) as Material
FROM

(txt, codepage is 1252, no labels, delimiter is '|', msq);

SAP_LEVEL:
LOAD @2 as SAP_Trans_code,
@2&'N'&@3 as Material_Trans_code,
@7 as Color_Size,
@12 as quantity_order,
@15/@12 as WS_Price
FROM

(txt, codepage is 1252, no labels, delimiter is '|', msq)
where @6='';

SAP_LEVEL_Final:
Concatenate(Orders)
Load
ApplyMap('SAPCode_Orders',SAP_Trans_code,'-') as SAPCode_Order,
ApplyMap('Material_Orders',Material_Trans_code,'-')&Color_Size as itemnumber,
Left(ApplyMap('Material_Orders',Material_Trans_code,'-'),3) as [Season Code],
applymap('MRP_temp',ApplyMap('Material_Orders',Material_Trans_code,'-')&Color_Size,'-')*quantity_order as Sales_price_order,
quantity_order,
WS_Price
Resident SAP_LEVEL;

drop table SAP_LEVEL;

Temporary:
mapping LOAD SAPCode as SAPCode_Order,
ETPCode
Resident Store_Mapping ;

Orders_temp:
load SAPCode_Order,
[Season Code],
itemnumber,
quantity_order,
Sales_price_order,
WS_price,
applymap('Temporary',SAPCode_Order,'Others') as ETPCode
resident Orders;

drop table Orders;

Orders_temp2:
left keep (Temp)
load
*
resident Orders_temp;

drop table Orders_temp;

Orders_data:
//Concatenate(POS_Master)
load
SAPCode_Order,
[Season Code],
itemnumber,
quantity_order,
Sales_price_order,
ETPCode
resident Orders_temp2;

Pos_Master_temp:
load *,
itemnumber&ETPCode as Key
resident Orders_data;

WS_Mapping:
mapping load itemnumber&ETPCode as Key,
WS_price
resident Orders_temp2;

POS_Master_final:
load *,
applymap('WS_Mapping',Key,'-') as WS_Cost
resident Pos_Master_temp;

//drop table POS_Master;
drop table Orders_data;
drop table Pos_Master_temp;

drop table Orders_temp2;

This is executed before incremental load

Re: how to execute incremental load?

Anil_Babu_Samineni — Wed, 22 Nov 2017 10:14:39 GMT

Okay, Seems working as expected until here. So, My question here is What is the business to use Increment Load over here? Can you attach Data model too?

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 10:33:21 GMT

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 10:34:40 GMT

Re: how to execute incremental load?

Anonymous — Wed, 22 Nov 2017 11:24:03 GMT

can you please help?