Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I'm new to Qlik and can't find out why the following script won't work:
SET vStartColumn = 2003;
SET vEndColumn = 2007;
SET vExcelName = "Budget";
FOR i = vStartColumn TO vEndColumn
LOAD
Office,
EmployeeID,
i AS Year, // This is where the error occurs.
"$(i)" AS Budget
FROM [lib://Datasources/$(vExcelName).xls]
(biff, embedded labels, header is 1 lines, table is $(vExcelName)$)
WHERE isnull("$(i)")=0 and "$(i)">0;
Next
The issue as I see it is when I try to use i as a numerical input in the load script. Somehow Qlik tries to turn it into a field name.
How can I solve this issue?
Kind regards.
Just use dollar sign expansion like in the other places:
LOAD
Office,
EmployeeID,
$(i) AS Year, // This is where the error occurs.
"$(i)" AS Budget
FROM [lib://Datasources/$(vExcelName).xls]
(biff, embedded labels, header is 1 lines, table is $(vExcelName)$)
WHERE isnull("$(i)")=0 and "$(i)">0;
Just use dollar sign expansion like in the other places:
LOAD
Office,
EmployeeID,
$(i) AS Year, // This is where the error occurs.
"$(i)" AS Budget
FROM [lib://Datasources/$(vExcelName).xls]
(biff, embedded labels, header is 1 lines, table is $(vExcelName)$)
WHERE isnull("$(i)")=0 and "$(i)">0;
Try,
LOAD
Office,
EmployeeID,
'$(i)' AS Year,
"$(i)" AS Budget
FROM [lib://Datasources/$(vExcelName).xls]
(biff, embedded labels, header is 1 lines, table is $(vExcelName)$)
WHERE isnull("$(i)")=0 and "$(i)">0;
Your script looked like a manual transforming of a crosstable. This could be easier handled with The Crosstable Load.
- Marcus
You can try
CrossTable (Year,Budget,2)
Load *
From Excel;
Thank you, this works perfectly.