topic Re: Filtering with generic If Statement in QlikView

Filtering with generic If Statement

markgraham123 — Mon, 12 Oct 2015 14:16:31 GMT

Hi all,

I have 4 fields Class1, Class2, Class3, and Class4 with ID.

I want to filter into 3 buckets based on their values.

If Class1 or Class2 or Class3 or Class4 if greater than 30, then ID as 30+ID,

similarly for other numeric values like 59, 100 etc..

But the if statement i use is not accurate enough and giving all the IDs as result.

Can someone pls look here.

It will be a great help.

Anonymous — Mon, 12 Oct 2015 14:22:25 GMT

Try something like this instead for your If's:

If((Class1 > 30 or Class2 > 30 or Class3 > 30 or Class4 > 30), ID) as [30+ ID],

If((Class1 > 30 or Class2 > 30 or Class3 > 30 or Class4 > 34), ID) as [34+ ID],

If((Class1 > 30 or Class2 > 30 or Class3 > 30 or Class4 > 52), ID) as [52+ ID],

If((Class1 > 30 or Class2 > 30 or Class3 > 30 or Class4 > 104), ID) as [104+ ID]

swuehl — Mon, 12 Oct 2015 14:23:07 GMT

You need to write your condition like

If( (Class1 >30 or Class2 >30 or Class3 > 30 or Class4 > 30), ID) as [30+ ID],

if( RangeMax(Class1, Class2, Class3, Class4) >30, ID) as [30+ ID]

avinashelite — Mon, 12 Oct 2015 14:26:12 GMT

Try like this

If((Class1 > 30 or Class2 > 30 or Class3> 30 or Class4 > 30), ID) as [30+ ID],

If((Class1> 34 or Class2> 34 or Class3> 34 or Class4 > 34), ID) as [34+ ID],

If((Class1> 52 or Class2> 52 or Class3> 52 or Class4 > 52), ID) as [52+ ID],

If((Class1> 104 or Class2> 104 or Class3 > 104or Class4 > 104), ID) as [104+ ID]

Anonymous — Mon, 12 Oct 2015 14:27:06 GMT

I think the second suggestion by swuehl using the RangeMax() is the best idea.

markgraham123 — Mon, 12 Oct 2015 14:28:22 GMT

Thanq Mr. Bill.

It's working.

markgraham123 — Mon, 12 Oct 2015 14:29:09 GMT

Thanq Mr. Avinash,

It's working. I learnt a new thing today.

markgraham123 — Mon, 12 Oct 2015 14:30:00 GMT

Thanq

This worked too.