4 Replies Latest reply: Aug 29, 2017 1:39 PM by Julio Andrés Arriaga Rangel RSS

    Cryptic error message: Syntax error

    Julio Andrés Arriaga Rangel

      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)

      Capture1.PNG

       

      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;