Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bertabbeel
New Contributor

Using index value in a FOR loop when loading data?

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Using index value in a FOR loop when loading data?

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;

5 Replies
MVP
MVP

Re: Using index value in a FOR loop when loading data?

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
Honored Contributor III

Re: Using index value in a FOR loop when loading data?

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?

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

- Marcus

shraddha_g
Honored Contributor III

Re: Using index value in a FOR loop when loading data?

You can try

CrossTable (Year,Budget,2)

Load *

From Excel;

bertabbeel
New Contributor

Re: Using index value in a FOR loop when loading data?

Thank you, this works perfectly.