master tables
Let currentdate =DATE(Today(1)-1,'YYYYMMDD');
Let Curr_Year = Year(Today());
Let Prev_Year = (Year(Today())-1);
//POS_Master:
//LOAD ETPCode,
// [inv no] ,
// Sold_Date ,
// Sold_Month ,
// Sold_Year ,
// itemnumber ,
// [Season Code] ,
// quantity_sold ,
// [sales price] ,
// [inv value] ,
// [Inv val Bucket],
// [tax amount],
// [return no],
// quantity_returns
//FROM
//
//(qvd)
//where Sold_Date <= MakeDate(2013,01,01) 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
(qvd);
*/
Store_SAP_Code:
mapping LOAD SAPCode,
[Store Name]
FROM
(qvd);
Store_Mapping:
LOAD SAPCode,
ApplyMap ('Store_SAP_Code',SAPCode,'') as [Store Name],
ETPCode,
UCB_Data_check,
[Distribution Channel],
[Bill To Name],
[Ship To Name],
Tier,
[Bill To State],
Store_ranking,
[Sales Group],
[Ship to State],
Brand_06,
Brand_0Y,
Condition_group4,
Country,
CustomerClassific,
District,
Group,
Interior_Designer,
[No of Floors],
No_of_Shop_Window,
[Operating Cost],
Outlet_store,
PostalCode,
Region,
[Rent (2016 %)],
[Rent (2016 A)],
[Rent (2017 A)],
[Rent(2017 %)],
[Shop Opening Year],
[Shop Postion],
[Stock Room Surface],
[Store Area],
Street,
[Active flag],
City
FROM
(qvd);
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=192.168.1.108;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];
Material_Master:
LOAD MATERIAL,
COLOR,
SIZE,
MATERIAL&COLOR&SIZE as itemnumber,
[Size Group],
Description,
COMPOSITION as Promotional_1,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
IF(GENDER ='LADIES', 'WOMEN', IF(GENDER='MENS','MENS', IF(GENDER='BOYS' or GENDER='GIRLS' or GENDER='BABY','KIDS', GENDER))) as Gender_Revised,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP as mrp_INR,
if (MRP <1000, '<1000', if (MRP <2000, '1000-2000',if (MRP <3000, '2000-3000',if (MRP <4000, '3000-4000',if (MRP <5000, '4000-5000',if (MRP <6000, '5000-6000', '>6000')))))) as [MRP Bucket]
FROM
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);
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;
Images:
bundle LOAD Material,
ImageLocation
FROM
(ooxml, embedded labels, table is Sheet1);
script uner te tab incremental load
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 ETPEASV55.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 ETPEASV55.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 ETPEASV55.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 ETPEASV55.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 ETPEASV55.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 ETPEASV55.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
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 ETPEASV55.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;
