topic Re: Nested If Statement in QlikView

Nested If Statement

markgraham123 — Mon, 21 Sep 2015 14:29:34 GMT

Hi, I was trying to do a nested if statement,

Where if the filed "Name"is null and "Filter count" >1 then i wanna display the field "Dept"

Else "Name"

Can some pls look into this.

If(Len(Trim(Name))=0,Dept, If([Filter Count] > 1, Dept, [Name])) as [New Column],

Re: Nested If Statement

sunny_talwar — Mon, 21 Sep 2015 14:34:57 GMT

May be this:

If(Len(Trim(Name)) = 0 or [Filter Count] > 1, Dept, Name) as [New Column]

My only question is: What is Filter Count? Is it a field in your database?

Re: Nested If Statement

markgraham123 — Mon, 21 Sep 2015 14:36:45 GMT

Filter count is the filed, where i'm checking in the script, if the ID has more than one Name.

If so, we wcannot display the same in the straight table with two values , so we will take Dept. instead of Name.

Re: Nested If Statement

Peter_Cammaert — Mon, 21 Sep 2015 14:42:03 GMT

You can add something like this as an expression:

=If (Count(Name) > 1, Dept, Name)

Re: Nested If Statement

markgraham123 — Mon, 21 Sep 2015 14:42:04 GMT

The new column is taking both the fields sunny.

Even after using the above statement,

Name and Dept.

So, we r having 2 values.

Re: Nested If Statement

markgraham123 — Mon, 21 Sep 2015 14:43:32 GMT

we need group by while using count right???

So, i'm doing a preceding load and using the new filed as "Filter Count"

Re: Nested If Statement

Peter_Cammaert — Mon, 21 Sep 2015 14:45:10 GMT

This trick works in the user interface.

If you need this in a Straight Table, you can simply use aggregation as the dimensions of the straight table will do the group-by anyway.

Re: Nested If Statement

markgraham123 — Mon, 21 Sep 2015 14:46:23 GMT

Yes, Peter.

But i wanna do that in script.

Since there were other calculations based on this filed.

Re: Nested If Statement

jonas_rezende — Mon, 21 Sep 2015 14:52:17 GMT

Hi, Mark Graham.

Try:

If(Len(Trim(Name)) = 0 and [Filter Count] > 1, Dept, Name) as [New Column]

I hope this helps!

Re: Nested If Statement

markgraham123 — Mon, 21 Sep 2015 15:01:54 GMT

Hi Jonas,

The above logic displays Dept. only if both the events occur at same time.

I.e. Len(Trim(Name)) = 0 and also filter count >1 ???

Re: Nested If Statement

jonas_rezende — Mon, 21 Sep 2015 15:10:04 GMT

Hi, Mark Graham.

Exactly.

How mentioned in your first post.

Where if the filed "Name"is null and "Filter count" >1 then i wanna display the field "Dept"

Else "Name"

Re: Nested If Statement

markgraham123 — Mon, 21 Sep 2015 15:21:43 GMT

Hi Jonas,

I'm really sorry.

Its like, if we encounter any of the Null or Filter count >1 , then "Dept" else name.

Its wokring with or statement, but the problem is, only for few records, i'm gretting both the name and Dept.

which is strange.

Re: Nested If Statement

sunny_talwar — Mon, 21 Sep 2015 16:02:01 GMT

Can you share some sample data where it is not working?

Re: Nested If Statement

MarcoWedel — Mon, 21 Sep 2015 23:50:39 GMT

another one:

If(IsNull(Name) or [Filter Count], Dept, Name) as [New Column]

hope this helps

regards

Marco

Re: Nested If Statement

Gabriel — Tue, 22 Sep 2015 10:30:20 GMT

Hi,

Try this

IF(LEN(Name) = 0 OR ISNULL(Name) AND count([Filter Count]) > 1, Dept, Name) AS [New Col]

Re: Nested If Statement

sunilkumarqv — Tue, 22 Sep 2015 10:36:12 GMT

try this

IF(Isnull(Name) or Filter count" >1 ,Dept,Name) as [New Column]

IF(Isnull(Name) ,IF( Filter count" >1 ,Dept,Name)) as [New Column]

Re: Nested If Statement

markgraham123 — Tue, 22 Sep 2015 16:54:27 GMT

Thanq sunil.

all the solutions worked.

Re: Nested If Statement

markgraham123 — Tue, 22 Sep 2015 16:54:46 GMT

Thanq Gabriel.

Re: Nested If Statement

markgraham123 — Tue, 22 Sep 2015 16:55:02 GMT

Thanq Marco.