topic Re: extract value from variable? in QlikView

extract value from variable?

Wed, 26 Feb 2014 00:51:30 GMT

Hey,

I encountered a problem, hoping I could find some answer here. Right now, I have 6 scenario (1,2,3,4,5,6), and for each scenario I created a variable with the name like vBest_sales_1 (the month with the highest sales under scenario 1).

Raw data and variables are as below:

date	scenario	sales	customer #
Sep-13	1	6	7
Oct-13	1	88	2
Nov-13	1	8	4
Sep-13	2	59	8
Oct-13	2	95	9
Nov-13	2	77	3
Sep-13	3	39	9
Oct-13	3	98	6
Nov-13	3	88	9
Sep-13	4	90	1
Oct-13	4	90	4
Nov-13	4	64	8
Sep-13	5	51	4
Oct-13	5	47	2
Nov-13	5	9	3
Sep-13	6	27	3
Oct-13	6	32	7
Nov-13	6	2	0

vBest_Sales_1	Oct-13
vBest_Sales_2	Oct-13
vBest_Sales_3	Oct-13
vBest_Sales_4	Sep-13
vBest_Sales_5	Sep-13
vBest_Sales_6	Oct-13

Right now, I want to create a table as below:

Scenario	1	2	3	4	5	6
sales	88	95	98	90	51	32
customer #	2	9	6	1	4	7

Current approach is to have a dimension which is calles metrix (metrix contains sales, customer #). And then I have 6 expressions (one for each scenario.)

Sample expression:

if(metrix = sales and date = vBest_Sales_1 and scenario =1, sales,

if(metrix= [customer #] and vBest_Sales_1 and scenario =1, [customer# ]))

I need to write 6 expressions for this chart, because for every expression ,the variable name is different. My question is can I use

vBest_Sales_'scenario' as the variable name? If scenario is 1, then I get the data Oct-13. I tried this. But QV always gives me vBest_Sales_1, not Oct-13.

I am going to expand the scenario to 15. I do not want to write 15 expression, any other ways to do this?

Thanks!!!!!!!

Re: extract value from variable?

jagan — Wed, 26 Feb 2014 01:56:16 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: extract value from variable?

Wed, 26 Feb 2014 03:06:32 GMT

That's definitely a good solution for this sample simplified problem. But it does not solve my problem. I simplified the problem I am facing right now. But in reality, it is much difficult. Can you answer my question directly like how to extract the value from the variable? That's what I really want to know.

Still, thank you for providing another solution.

Re: extract value from variable?

jagan — Wed, 26 Feb 2014 03:12:12 GMT

Hi,

I think it is not good practice to hardcode the variables, because the number of scenarios are dynamic, everytime if new scenario is added you need to add variables manually. I think the solution provided by me is flexible check with your original data.

Regards,

Jagan.

Re: extract value from variable?

rwunderlich — Wed, 26 Feb 2014 04:27:44 GMT

I don't know of any way to do an indirect reference to the variable in your example. However, you could generate a pick() expression in a variable that would return the correct result per Scenario. Do you generate the vBest_Sales_* variables in a script loop? I can show you how to generate the pick() expression but it almost seems easier to generate the values into a table. Any reason you're not creating these values as table fields?

-Rob