how to execute incremental load?

[Anonymous]

Hi

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

[Anil_Babu_Samineni]

What are you going to achieve?

[Anonymous]

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

[Anil_Babu_Samineni]

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

[Anonymous]

sold_date column is not present in this qvd

[Anonymous]

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

[Anil_Babu_Samineni]

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

[Anonymous]

how to do that?

[Anil_Babu_Samineni]

PFA

[Anonymous]

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