topic Re: Iterating through a variable in QlikView

Iterating through a variable

ritvik4BI — Mon, 08 Mar 2021 06:32:43 GMT

Hi All,

I have a variable vTagName = Alpha,Beta,Gamma

Now, I am loading a table which has the following columns: Timestamp, Alpha, Beta, Gamma, x,y andz. The requirement is that I have to load column Timestamp and only those columns present in the variable. i.e Alpha,Beta and Gamma in this case for which I am using the script the following script

Scenario:
LOAD timestamp,
$(vTagNames)

FROM
[Scenario.xlsx]
(ooxml, embedded labels, table is Sheet1);

This script is working fine but now I have a new requirement to read each TagName in one pass.

i.e. For the variable vTagNames.. in the first pass script should read Alpha, In the second iteration it should read Beta and in the third pass it should read Gamma and later join all three. How to do this?

Regards.

Re: Iterating through a variable

marcus_sommer — Mon, 08 Mar 2021 09:26:39 GMT

You could do it by treating the variable-content as array and looping through it, maybe like:

for i = rangesum(substringcount('$(vTagNames)', ','), 1)
let vField = subfield('$(vTagNames)', ',', $(i));
t: load [$(vField)], ... from ...;
next

- Marcus

Re: Iterating through a variable

ritvik4BI — Mon, 08 Mar 2021 10:20:27 GMT

Hi marcus_sommer, I am using the script below. I am getting the following errors as commented out in the script:

for i = rangesum(substringcount('$(vTagNames)', ','), 1) // getting a semantic error here
let vField = subfield('$(vTagNames)', ',', $(i));

Table4:

CrossTable(TagName, Data)

LOAD timestamp,
[$(vField)]

FROM
[Scenario.xlsx]
(ooxml, embedded labels, table is Sheet1);
next // The control statement is not correctly matched with its corresponding start statement.

Re: Iterating through a variable

marcus_sommer — Mon, 08 Mar 2021 11:04:33 GMT

Yes, I wasn't carefully enough and forgot to set the start-value, change it to:

for i = 1 to rangesum(substringcount('$(vTagNames)', ','), 1)
let vField = subfield('$(vTagNames)', ',', $(i));
t: load [$(vField)], ... from ...;
next

- Marcus