Cryptic error message: Syntax error

julioarriaga — Mon, 28 Aug 2017 00:46:48 GMT

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;

Re: Cryptic error message: Syntax error

effinty2112 — Mon, 28 Aug 2017 07:42:08 GMT

Hi Julio,

Try replacing

,$(vWeek) as Week

with

,'$(vWeek)' as Week

Cheers

Andrew

Re: Cryptic error message: Syntax error

swuehl — Mon, 28 Aug 2017 07:55:53 GMT

It seems that variable vWeek is also not set correctly. Check your code where you load the table and the following peek().

Re: Cryptic error message: Syntax error

Peter_Cammaert — Mon, 28 Aug 2017 09:35:05 GMT

Put an exit; statement after your vWeek assignment statement, and rerun your script. The script will stop at the exit statement which should work ok and give you a chance to inspect the different elements that (should) lead to a correct vWeek value

Use Settings->Variable Overview to check the contents of variable vWeek. It will either be empty or non-existant.

That means that the assignment didn't work or couldn't find any data in column GB_Week.

Start the table viewer by pressing Ctrl-T, right click table Table_Week and select "Preview". Does this table contain any data at all? If not, you know where to start fixing your code.

Good luck.

Peter

Re: Cryptic error message: Syntax error

julioarriaga — Tue, 29 Aug 2017 17:39:52 GMT

Thank you for your input, yes, there is an error in how I'm setting the variable.

topic Re: Cryptic error message: Syntax error in QlikView

Cryptic error message: Syntax error

Re: Cryptic error message: Syntax error

Re: Cryptic error message: Syntax error

Re: Cryptic error message: Syntax error

Re: Cryptic error message: Syntax error