topic Re: Sum Where Field Is Null in Data Model in QlikView

Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 17:19:48 GMT

Below is my data model:

If have created a straight table with the following fields:

I want to do some basic DQ calculations. Such as SUM AMOUNT USD when Contact ID is NOT NULL and Lead ID IS NULL.

Not having much luck after some research.

Any help is appreciated.

Re: Sum Where Field Is Null in Data Model

sunny_talwar — Thu, 08 Sep 2016 17:22:19 GMT

May be this:

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) > 0"}, [Lead ID] = {"=Len(Trim([Lead ID])) = 0"}>}[Amount USD])

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 17:26:01 GMT

thank you for the replay Sunny but this did not work. Not data is returned with this expression.

I can confirm there are records where CONTACT ID is NOT NULL and LEAD ID IS NULL

Re: Sum Where Field Is Null in Data Model

sunny_talwar — Thu, 08 Sep 2016 17:30:10 GMT

How about this:

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) > 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 17:31:58 GMT

Brilliant. Thank you.

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 17:42:29 GMT

Sunny - Does the syntax change if I want both fields to be blank? Trying to apply by changing > to = but it is not working

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 17:47:15 GMT

another idea:

--> Lead ID null(), Contact ID not null()

=sum({($-<[Lead ID]={"*"} >) * (<[Contact ID]={"*"} >)} [Amount USD])

--> both fields null():

=sum({($-<[Lead ID]={"*"} >) * ($-<[Contact ID]={"*"} >)} [Amount USD])

--> both fields not null():

=sum({(<[Lead ID]={"*"} >) * (<[Contact ID]={"*"} >)} [Amount USD])

Re: Sum Where Field Is Null in Data Model

sunny_talwar — Thu, 08 Sep 2016 17:48:25 GMT

This is not working?

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) = 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

You wanted rows where both Contact ID and Lead ID is null? Is that possible?

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 17:53:01 GMT

That is not working. I've tried several variations

Below is proof that these rows exist:

Re: Sum Where Field Is Null in Data Model

sunny_talwar — Thu, 08 Sep 2016 17:53:59 GMT

How about this:

Sum({<[Opportunity ID] = {"=Len(Trim([Contact ID])) = 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

Re: Sum Where Field Is Null in Data Model

swuehl — Thu, 08 Sep 2016 17:55:07 GMT

You can't select NULL, so the set modifier can't work.

You would need to select in another field that shows a unique value where the records fulfills the requirement.

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 17:57:25 GMT

This works. You are the best. Any insight into how you came to this answer?

Re: Sum Where Field Is Null in Data Model

sunny_talwar — Thu, 08 Sep 2016 18:00:24 GMT

From your screenshot I saw that you were looking at Contact ID and Lead ID against Opportunity ID and as Stefan mentioned that you need a non-null unique field to check against the nullness of another field.

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 18:00:36 GMT

thank you stefan. Sunny's suggestion worked immediately but the article by Henric is useful as always.

I am guessing that using the Len(Trim() solution may effect performance somewhere down the line?

Re: Sum Where Field Is Null in Data Model

Anonymous — Thu, 08 Sep 2016 18:01:54 GMT

Got it. So your expression could also be substituted with element function E(), that returns excluded values.

Very thankful again! Your help is always appreciated.

Re: Sum Where Field Is Null in Data Model

swuehl — Thu, 08 Sep 2016 18:11:35 GMT

Asking about performance, if OpportunityID is the primary key of your upper left table, I suspect that using set analysis will not perform much better than an ordinary aggregation with an if() clause,

I also think there can't be a case with OpportunityID linked to a LeadID value without also showing a ContactID, so it should be enough to check for ContactID being NULL.

=Sum(If( len(trim(ContactID))=0,Amount))