Using Field Value as Variable Name

[tahirkhalil]

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.

[settu_periasamy]

Hi,

check the Attachment. Do you need like that?

[maxgro]

like this? a Var2 slicer between Var3 and Var1?

[rwunderlich]

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

Pick(

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

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

)

-Rob

[effinty2112]

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

[tahirkhalil]

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?

[tahirkhalil]

Right Wob,

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

[tahirkhalil]

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?

[rwunderlich]

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

[tahirkhalil]

Hi

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?