Find records where multiple columns exceed benchmark

[Anonymous]

Hi everyone,

I was given a task to show records that have at least three of the specific four fields exceeding their benchmarks.

I have, in the loading script, created columns to indicate the benchmark status of each column needed (refer to attachment). And I am thinking of using script to create a new column to include any possibility of the combinations (when any three of the four fields exceed benchmark, plus when all the four columns exceed benchmark). However I would imagine this last column will be a VERY long script.

Is there any better way of doing this? Basically if any three or all four of these fields is marked as "Over BM xxxx" then they should be marked in the new column as "Risky".

Thanks in advance!

[guruprem · Accepted solution]

Would since it is creating a new field based on other three or four fields, a IF function would help.

Details:

I would do this way in one load statement I use IF conditions to produce 1 or 0 based condition on each field.

Then in preload I will use other IF  something like IF(Flag1+Flag2+Flag3+Flag4> 3 , 'Risk' , 'Not Risk') as IsRisk

[Anonymous]

Thanks Guruprem for your reply. It looks like a very simple and clever way. Although I probably will not use it in this case because I was still hoping to use text to indicate the flags, I will certainly keep it in mind for other applications.