topic Re: How to use pick function on a variable making a calculation? in QlikView

How to use pick function on a variable making a calculation?

Mon, 23 Mar 2015 11:46:21 GMT

Dear fellow Qlikers,

Here's my problem:

I have the following variable: vKPIs($1)

Which, when calculated, returns the following value:

$(vKPIs(Region='Europe'))=

'2334',' 45.1%',' 8.5',' 57.4%',' 55.7',' 79.0%',' 88.8%',' 3.1%',' 5.1',' 8.9%',' 84',' 11.6%',' 72.8%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%'

The problem is that, for some reason, when I use the pick formula, it doesn't recognize the comma separations in the calculation, hence giving me the following result:

pick(1, $(vKPIs(Region='Europe'))=

'2334',' 45.1%',' 8.5',' 57.4%',' 55.7',' 79.0%',' 88.8%',' 3.1%',' 5.1',' 8.9%',' 84',' 11.6%',' 72.8%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%',' 0.0%'

Instead of returning: pick(1, $(vKPIs(Region='Europe'))=2334, which is what I'd want.

Is there any way of getting the pick function to search the string AFTER the variable is calculated so I can get the result I'm looking for? Please let me know!

Thanks a lot in advance!

Sergio Peschiera

Re: How to use pick function on a variable making a calculation?

oknotsen — Mon, 23 Mar 2015 13:21:18 GMT

How about not using a variable, but making a table during the run of the script that contains both the region and the number you want it to relate to.

Re: How to use pick function on a variable making a calculation?

Mon, 23 Mar 2015 14:13:27 GMT

Hello Onno,

Thanks for your answer.

Although, I actually need to get all values from the list, which are all related to very region but answer to different Metrics. What I'd ultimately do is set a Dimension named ID with values (1-27) and then the expression would be pick(ID, $(vKPIs(Region='Europe')).