topic Re: Using Field Value as Variable Name in QlikView

Using Field Value as Variable Name

tahirkhalil — Thu, 26 Nov 2015 10:41:29 GMT

Hi,

I am supposed to define a dynamic threshold for compliance values. For that I am using inline table to define variables within the table, which is defined as variable for slicer.

See below the code:

[COMPRULE]:

LOAD * INLINE [

Variable, Value, StoreType

Var1, 5, A

Var2, 3, B

Var3, 2, C

];

// This is to set default values to variables:

Let x=NoOfRows('COMPRULE')-1;

Trace $(x);

For counter = 0 to NoOfRows('COMPRULE')-1

Trace $(counter);

Let Variable1 = peek('Variable', $(counter));

Let Value = peek('Value', $(counter));

Let $(Variable1) = $(Value);

Trace $(Variable1), $(Value);

Next counter

Now I need to access the corresponding value against each variable for calculation.

Can someone please check the attached doc and suggest me how can I get the value from field and get corresponding value from stored variable name.

Re: Using Field Value as Variable Name

settu_periasamy — Thu, 26 Nov 2015 16:29:25 GMT

Hi,

check the Attachment. Do you need like that?

Re: Using Field Value as Variable Name

maxgro — Thu, 26 Nov 2015 17:54:17 GMT

like this? a Var2 slicer between Var3 and Var1?

Re: Using Field Value as Variable Name

rwunderlich — Thu, 26 Nov 2015 18:39:40 GMT

For a specific StoreType you want to use the value of the corresponding variable?

Pick(

Match(StoreType, 'A', 'B', 'C')

,'$(Var1)','$(Var2)','$(Var3)'

)

-Rob

Re: Using Field Value as Variable Name

effinty2112 — Thu, 26 Nov 2015 19:37:08 GMT

Hi Tahir,

I'm not sure I understand what you want to do but if what you want is to judge compliance of each store based on a comparison of each store's score with the compliance rule value for each type of store there is no need to take these compliance rule values and place them into variables. The association on the field [Store Type] makes it straightforward. So straightforward I'm worried that I've totally failed to grasp what you require.

Isn't this the kind of result you want?

StoreType	Value	Compliance	StoreCode	Score
A	5	Yes	1	5
			10	7
			16	6
		No	4	2
			7	4
			13	2
			19	2
B	3	Yes	2	8
			8	3
			11	5
			17	5
		No	5	1
			14	1
			20	1
C	2	Yes	3	4
			9	8
			12	3
			15	8
			18	3

Re: Using Field Value as Variable Name

tahirkhalil — Mon, 30 Nov 2015 05:36:33 GMT

Hi Settu,

Great - quite helpful but what happened if I have multiple store types - e.g. more than 20? Can you suggest any alternative approach instead of Pick - because sometime these type can be changed and I will have to change the variable every time in that case?

Re: Using Field Value as Variable Name

tahirkhalil — Mon, 30 Nov 2015 05:37:34 GMT

Right Wob,

But what happened if I have n (unknown) number of store types?

Re: Using Field Value as Variable Name

tahirkhalil — Mon, 30 Nov 2015 05:39:21 GMT

Hi Andrew,

Yes, in case of tables, there is no issues, the problem arises when I need to change the values ie compliance standard through slicer. I need to see the impact of these changes in compliance. Am I able to clarify the point?

Re: Using Field Value as Variable Name

rwunderlich — Mon, 30 Nov 2015 15:01:57 GMT

I think Andrew has you on the right track, that is, the Compliance Value is linked to the StoreType. If you can have more than one ComplianceValue per StoreType, you just need to figure out which one to chose if more than one is possible. You could use Max(ComplianceValue ) or FirstSortedValue(ComplianceValue,..) depending on your business rule.

-Rob

Re: Using Field Value as Variable Name

tahirkhalil — Wed, 09 Dec 2015 07:40:39 GMT

I have taken the approach suggested by Settu & Rob.

Now I am stuck up with the button based filter of data. I have created two buttons to list either Compliant and Non-Compliant Stores.

Can someone suggest me how to manage the filters?

Re: Using Field Value as Variable Name

tahirkhalil — Mon, 14 Dec 2015 06:39:30 GMT

Can you please look into my last reply below and suggest anything?

Re: Using Field Value as Variable Name

tahirkhalil — Mon, 14 Dec 2015 06:40:10 GMT

Hi,

I need another help - can you please look into my last posting?