Operator as a Field Value

[#1]

I have a data set that includes a value, an operator, and a threshold.  Something like the below.

Value	Operator	Red_Threshold
0.0	>	0.0
1.0	>	0.0
97.5	<=	100.0
99.2	<	95.0
5	>	33

I am trying to do a count where the value breaches the Red_Threshold.  I have investigated using dollar-sign expansion in set analysis, but I am unfamiliar with dollar-sign expansion so I'm having some difficulty.

Any guidance would be greatly appreciated.

Thanks!

[sunny_talwar]

Do you have a sample app that we can take a look at?

[Anonymous]

Maybe something like the attached ?

This is the Text Color expression :

     = if ( Value $(=text(Operator)) Red_Threshold , Black() , Red() )

[#4]

Hi Bill - thanks for the quick response.  This seems like the right direction, but it looks like the calculation is not working properly.  

Value	Operator	Red_Threshold	= Value&Operator&Red_Threshold	Colour (Black is true, Red is false)	Expected Colour
0.0	>	0.0	0.0>0.0	Red	Red
1.0	>	0.0	1.0>0.0	Black	Black
5	>	33	5>33	Black	Red  - Why is this returning Black?
97.5	<=	100.0	97.5<=100.0	Black	Black
99.2	<	95.0	99.2<95.0	Black	Red - Why is this returning Black?

Maybe I am missing something.  Any thoughts?

[marcus_sommer · Accepted solution]

I think this won't work in this way - you couldn't build an expression(part) without using $-sign expansion which meant a variable will be created which could only have one value at a time and which will be evaluated before the chart will be calculated and then applied to each row.

Therefore you need another method like these with pick(match()) - see also the attachment:

= pick(match(Operator, '>', '>=', '=', '<=', '<'),

    if(Value > Red_Threshold, 'Black', 'Red'),

    if(Value >= Red_Threshold, 'Black', 'Red'),

    if(Value = Red_Threshold, 'Black', 'Red'),

    if(Value <= Red_Threshold, 'Black', 'Red'),

    if(Value < Red_Threshold, 'Black', 'Red'))

- Marcus

[Anonymous]

Marcus

Thanks for chipping in.  My way looked promising, but does not work properly.

[#7]

Thanks Marcus and Bill.

I ended up creating a breached flag  field in the script using an if statement similar to what you provided Marcus.

[rwunderlich]

Can you do this in the script or does it have to be done in a chart?

-Rob

[#9]

I created the flag in the script.  Basically just created a new dimension.

    if((((Red_Threshold_Operator = '>') and (Value > Effective_Red_Threshold)

  or (Red_Threshold_Operator) = '>=' and (Value >= Effective_Red_Threshold)

  or (Red_Threshold_Operator) = '<' and (Value < Effective_Red_Threshold)

  or (Red_Threshold_Operator) = '<=' and (Value <= Effective_Red_Threshold))and Effective_Red_Threshold <> ''),'Breached',

  if((((Amber_Threshold_Operator = '>') and (Value > Effective_Amber_Threshold)

  or (Amber_Threshold_Operator) = '>=' and (Value >= Effective_Amber_Threshold)

  or (Amber_Threshold_Operator) = '<' and (Value < Effective_Amber_Threshold)

  or (Amber_Threshold_Operator) = '<=' and (Value <= Effective_Amber_Threshold))and Amber_Threshold_Operator <> '')

  ,'Amber','Not Breached')) as BreachedFlag,

pretty messy but it worked for me.

[rwunderlich]

I was thinking of something like this:

Data:

LOAD

  *

  ,-Evaluate(Value & Operator & Red_Threshold) as Breached

;

LOAD

  Value,

    Operator,

    Red_Threshold

FROM

[https://community.qlik.com/thread/199440]

(html, codepage is 1252, embedded labels, table is @1);

-Rob

http://masterssummit.com

http://qlikviewcookbook.com