Using index value in a FOR loop when loading data?

[Anonymous]

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.

[swuehl · Accepted solution]

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;

[shraddha_g]

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;

[marcus_sommer]

Your script looked like a manual transforming of a crosstable. This could be easier handled with The Crosstable Load.

- Marcus

[shraddha_g]

You can try

CrossTable (Year,Budget,2)

Load *

From Excel;

[Anonymous]

Thank you, this works perfectly.