topic Loop from an array in QlikView

Loop from an array

Mon, 05 Jul 2010 18:21:27 GMT

I am trying to build a script with a loop based on a variable array. My variable can be set as: vYears= '2008,2009,2010'. The loop should then read 2008 and do something with it, read 2009 and do something with it etc. The set of variables is defined by a user. So, I need first to read the array, create a new variable with each year and loop.

Thanks!

Loop from an array

Mon, 05 Jul 2010 18:34:24 GMT

for i=1 to substringcount ( vYears, ',' )

subfield(vYears, ',' ,i)

Something like this!!!

Loop from an array

Mon, 05 Jul 2010 18:40:12 GMT

Hi Patrick, you can also use the for each syntax. To accomplish this, you would have to declare your variable similar to:

set vYears = '2008','2009','2010'

Then use the for each:

for each y in $(vYears)

statements...

Regards

Loop from an array

Mon, 05 Jul 2010 21:06:13 GMT

Hi Ivan, this works finr for years, thanks. However, if I have:

set vSomething = 'aaa', 'bbb';

for each s in $(vSomething) I get the value NULL for s.

Any clue?

Loop from an array

Mon, 05 Jul 2010 21:15:25 GMT

Hi Patrick, it strongly depends in the context you use the variable 's', two general scenarios come to my mind:

1. If you expanded the variable like this:

$(s) , qlikview would look for a variable named aaa in the first iteration, since it doesnt find such variable it returns null.

2. if you can to use the s variable as an string, you have to encapsulate the expansion into single quotes, like this:

'$(s)', this way the value would be render as an string.

Hope this helps you.

Regards

Loop from an array

Mon, 05 Jul 2010 21:22:20 GMT

My code was missing the single quote as you explained. Thanks!

Loop from an array

Tue, 06 Jul 2010 08:52:29 GMT

I have to add something to the discussion:

If the variable vStuff has 2+ string parameter. The iteration using a for...next works fine. However, if there is only one parameter, it returns NULL.

Assume the following code:

for each s in $(vStuff)

something happens

If vStuff = '2010'; - works fine

If vStuff = '2009', '2010'; - works fine

If vStuff = 'aaa','bbb'; - works fine

If vStuff = 'aaa'; Doesn't work. s is set to NULL.

Is this supposed to be a normal behaviour?

Loop from an array

Tue, 06 Jul 2010 14:48:19 GMT

Interesting, it never ocurred to me before. What I see, is that when you expand a single token string, QlikView again thinks is the name on a variable, to solve this problem, you can declare your variable as follows:

set vStuff="'aaa','bbb'";

set vStuff="'aaa'";

Notice the declaration is the same as before, but with an addition of double quotes encapsulating the tokens, this way you should not have any problem, no matter the quantity of tokens.

Regards