Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
julioarriaga
Contributor II

Cryptic error message: Syntax error

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;

1 Solution

Accepted Solutions
MVP
MVP

Re: Cryptic error message: Syntax error

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

View solution in original post

4 Replies
effinty2112
Honored Contributor

Re: Cryptic error message: Syntax error

Hi Julio,

Try replacing

,$(vWeek) as Week


with


,'$(vWeek)' as Week


Cheers


Andrew

MVP
MVP

Re: Cryptic error message: Syntax error

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

View solution in original post

Re: Cryptic error message: Syntax error

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

julioarriaga
Contributor II

Re: Cryptic error message: Syntax error

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