Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Thanks in advance for your help. I'm losing my mind over this problem. I am trying a resident load from a table I created from a binary, but I'm keep getting a syntax error and I can't find where the error is! (Image attached)
I attached the whole code (sorry for the suboptimal structure but I'm still a rookie):
//Last updated date 24/aug/2017. Developed by Julio Arriaga.
//This code creates two layouts. //The first one is a qvd generated for its use in the Corporate Sales apps.
//The other one is a txt file used by Manuel Mendez Sanchez in Marketing, agreggating sales by Product and Week.
//This executes the binary statement.
binary [..\..\Models\Comercial\P&L\CB\Model_P&L_CB_GROUP.qvw];
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET MonthNames='ene.;feb.;mar.;abr.;may.;jun.;jul.;ago.;sep.;oct.;nov.;dic.';
SET DayNames='lun.;mar.;mié.;jue.;vie.;sáb.;dom.';
// This establishes the dates.
//let vRunFrom = Date(Today()-7); //This line is used for production.
//let vRunTo = Date(Today()-1); //This line is used for production.
let vRunFrom = Date(Date#('17/08/2017')); //This line is used for tests.
let vRunTo = Date(Date#('23/08/2017')); //This line is used for tests.
//This is used to to obtain the correspondent GB Week.
//This is used because not all records in the Fact table have assigned a GB_Week value for a reason that I don't know (I didn't develop this model).
Table_Week:
load Date(Bill_Date) as Bill_Date
,max(GB_Week) as GB_Week
resident Fact
where 1=1
and Date(Bill_Date)='$(vRunFrom)'
group by Bill_Date;
//This is to store the correspondent GB Week in a variable.
Let vWeek = peek('GB_Week',0,'Table_Week');
map_Sales_District:
mapping load Sales_District
,Sales_District_Name
resident Sales_District;
map_ChannelID:
mapping load Customer_Key
,Channel_ID
resident Customer;
map_ChannelID2Name:
mapping load Channel_ID
,Channel_Name
resident ChannelID2Name;
map_ChannelName:
mapping load Customer_Key
,applymap('map_ChannelID2Name',Customer_Key,'Error')
resident Customer;
map_BrandName:
mapping load Product_ID
,Brand_Name
resident Product;
//This is to create a temporal table to aggregate by the different divisions needed by Corporate Sales.
//The temp is created in order to filter by Bill_Date and then build the aggregated tables by Week, discarding Bill_Date.
//Although this method is more troublesome and not efficient, it was chosen in order to guarantee the correct representation of the data because I don't know thouroughly the model.
Temp:
load Product_ID
,date(Bill_Date) as Bill_Date
,Customer_Key
,Sales_District as Region //I need to properly map this field beause right now it is numerated by single digits.
,applymap('map_Sales_District',Sales_District,'Error') as [Region Name]
,if(Currency='CAD','102',Currency) as [Id Currency] //I need to properly map this field.
,Currency as [Currency Name]
,'CB' as Pais //I need to properly map this field.
,'127' as Id_Country
,applymap('map_ChannelID',Customer_Key,'Error') as [Channel ID]
,applymap('map_ChannelName',Customer_Key,'Error') as [Channel Name]
,applymap('map_BrandName',Product_ID,'Error') as [Brand Level4]
,sum(Total_Sales) as Total_Sales
,sum(Net_Invoice_Qty) as Net_Invoice_Qty
,sum(Total_Returns) as Total_Returns
,sum(Return_Invoice_Quantity) as Return_Invoice_Quantity
,sum(Net_Sales) as Net_Sales
Resident Fact
where 1=1
and date(Bill_Date)>='$(vRunFrom)'
and date(Bill_Date)<='$(vRunTo)'
group by Product_ID,date(Bill_Date),Customer_Key,Sales_District,applymap('map_Sales_District',Sales_District,'Error'),if(Currency='CAD','102',Currency)
,Currency,'CB','127',applymap('map_ChannelID',Customer_Key,'Error'),applymap('map_ChannelName',Customer_Key,'Error'),applymap('map_BrandName',Product_ID,'Error');
//This is the aggregation by Week, created from the Temp table.
Corp_Sales:
load Product_ID
,year(Bill_Date) as Fiscal_Year
,$(vWeek) as Week
,Customer_Key
,Region
,[Region Name]
,[Id Currency]
,[Currency Name]
,Pais
,Id_Country
,[Channel ID]
,[Channel Name]
,[Brand Level4]
,sum(Total_Sales) as Total_Sales
,sum(Net_Invoice_Qty) as Net_Invoice_Qty
,sum([Total Returns]) as [Total Returns]
,sum(Return_Invoice_Quantity) as Return_Invoice_Quantity
,sum(Net_Sales) as Net_Sales
resident Temp
group by Product_ID,year(Bill_Date),$(vWeek),Customer_Key,Region,[Region Name],[Id Currency],[Currency Name],Pais,Id_Country
,[Channel ID],[Channel Name],[Brand Level4];
//This is used to store the information for the Corporate Sales Layout.
STORE Corp_Sales into [..\USR_Files\Mapping_Files\Marketing\InputDevelopCom\CB_P&L_SalesCustomer_$(vWeek).qvd] (qvd);
//This is to create a final table aggregated by Product, Week and Year (from Bill_Date) from the temporal table, dropping Bill_Date (needed for the layout).
Sales_MktTxt:
LOAD Product_ID
,year(Bill_Date) as Fiscal_Year
,Week
,sum(Total_Sales)
,sum(Net_Invoice_Qty)
,sum(Total_Returns)
,sum(Return_Invoice_Quantity)
,sum(Net_Sales)
Resident Sales
group by Product_ID,year(Bill_Date),$(vWeek);
//This is used to store the information as a particular name.
STORE Sales_MtTxt into [..\USR_Files\Mapping_Files\Marketing\MarketingTxt\IC_MKT_VEN_CB_$(vWeek).txt] (txt, delimiter is '|');
//Dropping of unused tables.
drop table Metrics,Product,CalendarWeek,CalendarDay,Fact,Definition_Table,Sales_District,Customer,Product_Dimention,Customer_Dimention,Sales,Time_Dimension,Sales_Dimension,ChannelID2Name,Sales_Customer;
Accepted Solutions
