-
Re: Using index value in a FOR loop when loading data?
Stefan Wühl Nov 29, 2017 2:48 PM (in response to Bert Abbeel)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;
-
Re: Using index value in a FOR loop when loading data?
Shraddha Gajare Nov 30, 2017 12:22 AM (in response to Stefan Wühl )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;
-
Re: Using index value in a FOR loop when loading data?
Bert Abbeel Nov 30, 2017 9:11 AM (in response to Shraddha Gajare )Thank you, this works perfectly.
-
-
-
Re: Using index value in a FOR loop when loading data?
Marcus Sommer Nov 30, 2017 1:06 AM (in response to Bert Abbeel)Your script looked like a manual transforming of a crosstable. This could be easier handled with The Crosstable Load.
- Marcus
-
Re: Using index value in a FOR loop when loading data?
Shraddha Gajare Nov 30, 2017 1:17 AM (in response to Bert Abbeel)You can try
CrossTable (Year,Budget,2)
Load *
From Excel;