Iterating through a variable

[ritvik4BI]

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.

[marcus_sommer]

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

[ritvik4BI]

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.

[marcus_sommer · Accepted solution]

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