topic Re: Operator as a Field Value in QlikView

Operator as a Field Value

Wed, 13 Jan 2016 17:00:34 GMT

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!

Re: Operator as a Field Value

sunny_talwar — Wed, 13 Jan 2016 17:30:05 GMT

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

Re: Operator as a Field Value

Anonymous — Wed, 13 Jan 2016 18:09:25 GMT

Maybe something like the attached ?

This is the Text Color expression :

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

Re: Operator as a Field Value

Wed, 13 Jan 2016 18:50:52 GMT

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?

Re: Operator as a Field Value

marcus_sommer — Thu, 14 Jan 2016 13:06:59 GMT

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

Re: Operator as a Field Value

Anonymous — Thu, 14 Jan 2016 13:15:45 GMT

Marcus

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

Re: Operator as a Field Value

Thu, 14 Jan 2016 16:28:36 GMT

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.

Re: Operator as a Field Value

rwunderlich — Thu, 14 Jan 2016 16:59:35 GMT

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

-Rob

Re: Operator as a Field Value

Thu, 14 Jan 2016 21:37:04 GMT

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.

Re: Operator as a Field Value

rwunderlich — Thu, 14 Jan 2016 22:32:25 GMT

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