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

    Cryptic error message: Syntax error

    Julio Arriaga

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


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




      mapping load Sales_District


      resident Sales_District;




      mapping load Customer_Key


      resident Customer;




      mapping load Channel_ID


      resident ChannelID2Name;




      mapping load Customer_Key


      resident Customer;






      mapping load Product_ID


      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.


      load Product_ID

      ,date(Bill_Date) as Bill_Date



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






      //This is the aggregation by Week, created from the Temp table.


      load Product_ID

      ,year(Bill_Date) as Fiscal_Year

      ,$(vWeek) as Week



      ,[Region Name]

      ,[Id Currency]

      ,[Currency Name]



      ,[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).


      LOAD Product_ID

      ,year(Bill_Date) as Fiscal_Year







      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;