Complex nested If statements in Qlik

I was working with a client that was having problems working on a fairly complex nested IF statement. I thought that my method of doing those was a really stupid way, but it always worked for me. I showed him the technique and he was able to get his complex if working in very short order. Well, as the saying goes – a stupid idea that works, is NOT a stupid idea.

The example was asymmetrical categories based on number of days.

What i do is to start off writing each part of the if statement like this

If(c, t, f) as myCategory;

c = condition, t = true, f=false

Next, i start to fill things in

if(DaysVariance < -60, ‘really old’, ‘less than -60’) as myCategory;

Next, drop in the next condition

if(DaysVariance < -60, ‘really old’, if(c,t,f)) as myCategory;

This does two things – first and most important – it gets your parenthesis straight, and second – it gives you place holders so you don’t lose track of your conditions.

if(DaysVariance < -60, ‘really old’, if(DaysVariance < -10, ‘sort of old’, ‘less than 10’)) as myCategory;

I use these little text descriptions as reminders as what i am working with at the moment. It doesn’t matter what you wind up with as your values for your end result – using these little reminders helps you keep things straight. Just keep substituting the if(c,t,f) construct wherever you need to go one level deeper, then fill in the blanks afterwards.

How well did it work?

The client was able to get an 8 condition nested if statement working on the first try in less than 30 minutes – a new record for him.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s