list of columns in variable for use in ValueList - better method?

[david_kesselhei]

Hi,

I need to build a ValueList containing 11 measures because I want to put those measures in the same chart. That means I'll need to build a nested If() for all 11 measures and I would like that to be as easy to read as possible.

What I have so far:

I was able to define a a variable which does what I want it to do:

=CHR(39) & 'Impressions' & CHR(39) & CHR(44) & CHR(39) & 'Impressions' & CHR(32) & 'Unique' & CHR(39) 

Which evaluates to 

'Impressions','Impressions Unique'

I can now use this variable (let's call it vImpressions) in a ValueList:

ValueList(vImpressions)

While it does work it seems a bit too much work to build the list that way. 

Question:

Is there an easier way to build a variable containing a list that can be passed to a function?

Thank you for your help.

[mohan_1105]

Hi David,

In case, if the list is too many I would prefer to write down all the list in the inline table and based on how the expression change for all the list I write the expression. 

In few occasion, I ignored writing valuelist by using crosstable in the script. This is also based on the expression.

Regards,

Mohan

[albert_guito]

Hi,

As @mohan_1105 has suggested you can use an Inline table as

MyList:
Load CHR(39) & Concat(Impressions,CHR(39)&CHR(44)&CHR(39)) & CHR(39) as Array;
Load * Inline [Impressions
Impressions
Impressions Unique

];

LET vImpressions=Peek('Array',-1,'MyList');
DROP Table MyList;

With it you just only need to create the dimension with =ValueList(vImpressions) expression

Ag+

[sunny_talwar · Accepted solution]

I wonder why you have to use Chr()s here? Why can't you just have a variable which looks like this

'Impressions','Impressions Unique'

and may be use it like this

ValueList($(vImpressions))

Screenshot from QlikView... but should work the same way in Qlik Sense

 

[albert_guito]

ah ok, you can create the array manually but I've thought it would be interesting show how to get them using other strategies.

I use this with for getting data out of qlik.

Ag+

[sunny_talwar]

Yours is a method to do this as well... but what's the difference of listing the values directly into a variable vs a variable created in the script using an Inline load? If it was using only Inline load (Island table) and used instead of value list... then I see a use, but both are solutions are similar 🙂

[albert_guito]

Jejjeje, I agree with you. Using an in line or list of values in a variable needs the same effort.

We can say this is an academic sample.

[david_kesselhei]

This was the very first thing I tried and it didn't work. To show the exact error I decided to recreate it and post it here.

And guess, it works now, without any errors.... 

[marcus_sommer]

I wouldn't use valuelist() for such an approach else loading the items in a field (maybe MeasureList) within an island-table (means any table which isn't associated with the datamodel) and then you could use a quite simple expressions like:

pick(match(MeasureList, 'Measure1', 'Measure2', ...), Expression1, Expression2, ...)

- Marcus

[albert_guito]

Right, but an Alternate State can be used for this ...