How to compare two values where operator is stored as field value.

[#1]

Hi All,

I need some script help. Below you will see my data setup.

test:

LOAD * INLINE [

A, COMPARE, THRESHOLD

100, >=, 200

500, >=, 200

];

I am attempting to create a field called "Flag" if value in field "A" when compared using OPERATOR in field "COMPARE" against field "THRESHOLD" should give me 1 if it satisfies the condition.

I tried to use dollar sign expansion within script and that failed. Any other solutions that you can recommend would be appreciated.

if ((A$(=COMPARE)THRESHOLD)=TRUE(),1,0) --> script error (Internal Error)

Thank you.

Pranita

[jonathandienst]

Pranita

Never tried this , and it may not be possible. But try my suggestions below:

Two suggestions:

1. $(=COMPARE) should read $(COMPARE).

2. $(COMPARE) will only return a value if there one value in COMPARE (eg in a table/chart dimensioned by one of the fields in the table.) Create a text box with =$(COMPARE) as the text to see what I mean.

Good luck

Jonathan

[#3 · Accepted solution]

Hi,

try this:

test:
LOAD * INLINE [
A, COMPARE, THRESHOLD
100, >=, 200
500, >=, 200
];

test2:
load pick(match(COMPARE,'>=','<=','>','<'),
if(A >= THRESHOLD,1,0),
if(A <= THRESHOLD,1,0),
if(A > THRESHOLD,1,0),
if(A < THRESHOLD,1,0)
) as flag,
*
resident test;

drop table test;

best regards!

[johnw]

LOAD *, -evaluate("A"&"COMPARE"&"THRESHOLD") as "Flag"
INLINE [
A, COMPARE, THRESHOLD
100, >=, 200
500, >=, 200
100, <, 200
100, <>, 200
];

[#5]

In a chart

= if (A $(=COMPARE) THRESHOLD, 'True', 'False')

[johnw]

Nick Bor wrote:if (A $(=COMPARE) THRESHOLD, 'True', 'False')

Heh.

In this case, since there are multiple values for COMPARE, COMPARE returns null, $(=COMPARE) returns "-", and we end up with if(A - THRESHOLD,'True','False'). So so multiple compare values behaves as if compare is always "<>". Granted, the example only had a single value for COMPARE, but I assume the point of making this dynamic is that there can be multiple values. Best I can think of for a chart is the pick(match()) that Gabriela mentioned, but just put it in the chart instead of in the load. I'd do it during the load, though.

[#7]

Yeah, I got it right after the post 🙂

[#8]

Thank you all for your quick response

@ Gabriela: Your response got me going. Thank you again!!

Here is another approach my co-worker came up with

testcomp:

load *,

if(sign(evaluate(compexp))<0,'true','false') as compresult;

load *,

actvalue & compoperator & compvalue as compexp;

LOAD * INLINE [

actvalue, compoperator, compvalue

50000, >, 20000

50000, <, 20000

20000, >=, 20000,

];

actvalue	compoperator	compvalue	compexp	compresult
20000	>=	20000	20000>=20000	true
50000	<	20000	50000<20000	false
50000	>	20000	50000>20000	true