topic Re: Unexpected Token in QlikView

Unexpected Token

AkbarShaikh — Sat, 16 Nov 2024 19:24:22 GMT

Hello guys,

I have purchased this book Qlikview: Advanced Data Visualization and as an example, it is creating a random set of data.

I am scripting as per the book, but still getting an Unexpected token error. I have tried every option to resolve this, but not able to get past this error.

I have attached the script and the error message for reference.

Any help would be highly appreciated.

Thanks,

Akbar

// ***************This is the script as mentioned in the book ********************** //

//Calculate random number of lines
LET vPCount = Floor(Rand () * $(vDateFactor)) + 1;

FOR L = 1 to $(vPCount)
//Calculate random values
LET vQty = Floor (Rand() * (50+ $(vDateFactor))) +1;
LET vRnd = Floor (Rand() * $(vProdCount));
LET vPID = Peek ('ProductID', $(vRnd), 'Product');
LET vCost = ApplyMap('Product_Cost_Map', $(vPID), 1); // *****This is where the error hits in *****//
LET vPrice = ApplyMap('Product_Price_Map' ,$(vPID), 1);

Re: Unexpected Token

Arthur_Fong — Mon, 23 Dec 2019 13:51:34 GMT

Try this:

LET vCost = ApplyMap('Product_Cost_Map', '$(vPID)', 1);
LET vPrice = ApplyMap('Product_Price_Map' ,'$(vPID)', 1);

Re: Unexpected Token

AkbarShaikh — Tue, 24 Dec 2019 07:56:16 GMT

Thank you for the reply, I had tried using ' ' on the variable, but it still gives an error.

Re: Unexpected Token

Arthur_Fong — Tue, 24 Dec 2019 09:57:20 GMT

Applymap always go along with a mapping table.

Do you have a mapping table above your script?

Eg:

Product_Cost_Map:

Mapping load

Key,MapField

inline [

Key,MapField

Key1,A

Key2,B

];

Re: Unexpected Token

AkbarShaikh — Tue, 24 Dec 2019 14:23:12 GMT

Hi Fong, I have a preceding Mapping Load of the with both the table names separately with two fields. //************This is the preceding load script*****// Product_Cost_Map: Mapping Load ProductID, NUM(CostPrice) Resident Products; //This table is already loaded with ProductID, CostPrice, SalesPrice Product_Price_Map: Mapping Load ProductID, NUM(SalesPrice) Resident Products;

Re: Unexpected Token

Arthur_Fong — Tue, 24 Dec 2019 14:36:35 GMT

Can you post the full script?

Re: Unexpected Token

AkbarShaikh — Tue, 24 Dec 2019 14:55:04 GMT

Sure Fong,

Here it is...

//***** Script ***** //

//**** Generating some data ranges to be used in calculation ***** //
LET vStartYear = 2009;
LET vEndYear = Year(Now());
LET vStartDate = Floor(MakeDate($(vStartYear)), 4, 1);
LET vEndDate = Floor(MakeDate($(vEndYear)), 3, 31);
LET vNumDays = vEndDate - vStartDate +1;

Products:
LOAD ProductID,
Product,
CategoryID,
SupplierID,
Money# (CostPrice, '$#,##0.00', '.', ',') as CostPrice,
Money# (SalesPrice, '$#,##0.00', '.', ',') as SalesPrice
FROM
Products.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Product_Cost_Map:
MAPPING LOAD
ProductID,
NUM(CostPrice)
RESIDENT Products;

Product_Price_Map:
MAPPING LOAD
ProductID,
NUM(SalesPrice)
RESIDENT Products;

Customer:
LOAD CustomerID,
Customer,
City,
Country,
Region,
Longitude,
Latitude,
Geocoordinates
FROM
Customers.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Employee:
LOAD EmployeeID,
Employee,
Grade,
SalesUnit
FROM
Employees.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE MATCH (Grade, 0, 1, 2, 3); //SALES PEOPLE

//***** COUNT ID records in each table *****//
LET vCustCount = FieldValueCount('CustomerID');
LET vProdCount = FieldValueCount('ProductID');
LET vEmpCount = FieldValueCount('EmployeeID');

// Create a loop of 10,000 iterations

FOR i = 1 to 10000

// 'A' type of records are for any date /time

// Grab a random employee and customer
LET vRnd = Floor (Rand () * $(vEmpCount));
LET vEID = Peek ('EmployeeID', '$(vRnd)', 'Employee');

LET vRnd = Floor (Rand () * $(vCustCount));
LET vCID = Peek ('CustomerID', '$(vRnd)', 'Customer');

//Create a date for any time of day between 9 - 5
LET vOrderDate = $(vStartDate) + Floor (Rand () * $(vNumDays)) + ((9/ 24) + (Rand () / 3));

//Calculate a random freight amount
LET vFreight = ROUND(RAND () * 100, 0.01);

//Create the header record
OrderHeader:
LOAD
'A' & $(i) as OrderID,
$(vOrderDate) as OrderDate,
$(vCID) as CustomerID,
$(vEID) as EmployeeID,
$(vFreight) as Freight
AutoGenerate (1);

//Genereate Order Lines
//This factor allows us to generate adiiferent number of lines depending on the day of the week

LET vWeekDay = NUM (WeekDay ($(vOrderDate)));
LET vDateFactor = Pow(2, $(vWeekDay))*(1-(Year(Now())-Year($(vOrderDate)))*0.05) ;

//Calculate random number of lines
LET vPCount = Floor(Rand () * $(vDateFactor)) + 1;

FOR L = 1 to $(vPCount)
//Calculate random values
LET vQty = Floor (Rand() * (50+ $(vDateFactor))) +1;
LET vRnd = Floor (Rand() * '$(vProdCount)');
LET vPID = Peek ('ProductID', '$(vRnd)', 'Product');
LET vCost = ApplyMap('Product_Cost_Map', '$(vPID)', 1);
LET vPrice = ApplyMap('Product_Price_Map' ,'$(vPID)', 1);

OrderLine:
LOAD
'A' & $(i) as OrderID,
$(L) as LineNo,
$(vPID) as ProductID,
$(vQty) as Quantity,
$(vPrice) as SalesPrice,
$(vCost) as SalesCost,
$(vQty)* $(vPrice) as LineValue,
$(vQty)* $(vCost) as LineCost
AutoGenerate (1);

NEXT
//"B" type records are for summer peak
// Summer Peak - Generate additional records for summer months to stimulate a peak trading period

LET vY = Year($(vOrderDate));
LET vM = Floor (Rand()*2)+7;
LET vD = Day ($(vOrderDate));
LET vOrderDate = Floor (MakeDate ($(vY), $(vM), $(vD))) + (9/24) + (Rand() /3);

IF Rand() > 0.8 THEN
// Grab a random employee and customer
LET vRnd = FLoor (Rand ()* $(vEmpCount));
LET vEID = Peek ('EmployeeID', $(vRnd), 'Employee');
LET vRnd = Floor (Rand() *$(vCustCount));
LET vCID = Peek ('CustomerID', $(vRnd), 'Customer')

//***** END OF SCRIPT *****//

P.S.

This script is from the book Qlikview Advance Data Visualization.

I am running the script with a debugger now - will let you know if I get a solution

Cheers,

Re: Unexpected Token

AkbarShaikh — Tue, 24 Dec 2019 15:21:22 GMT

Hi Fong,

I found the error: Table name in Peek function was incorrect:

FOR L = 1 to $(vPCount)
//Calculate random values
LET vQty = Floor (Rand() * (50+ $(vDateFactor))) +1;
LET vRnd = Floor (Rand() * '$(vProdCount)');
LET vPID = Peek ('ProductID', '$(vRnd)', 'Product'); // The table name is Products
LET vCost = ApplyMap('Product_Cost_Map', '$(vPID)', 1);
LET vPrice = ApplyMap('Product_Price_Map' ,'$(vPID)', 1);

The script did run without given any 'token' error but has created a Syn key on Product Id and Sales Price.

If I rename these two fields, the Product table does not connect to the fact table (Order Header).

I have attached the table view for your reference.