Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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.
Try this:
LET vCost = ApplyMap('Product_Cost_Map', '$(vPID)', 1);
LET vPrice = ApplyMap('Product_Price_Map' ,'$(vPID)', 1);
Thank you for the reply, I had tried using ' ' on the variable, but it still gives an error.
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
];
Can you post the full script?
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,
a
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.