topic Re: Null in set analysis in QlikView

Null in set analysis

Wed, 18 Nov 2015 12:04:12 GMT

Good Day

=Count(DISTINCT{$<[Closing date]={"<=$(=Today(2))"} and [Candidate Name]={"null()"}>}Spec)

I am trying to write a code that will return number of specs where closing date is less than today and candidate name in empty,but the code above has errors,

Please advise how do i do it.

Thanks and Regards

Priscilla

Re: Null in set analysis

sunny_talwar — Wed, 18 Nov 2015 12:23:25 GMT

Try this may be:

=Count(DISTINCT{$<[Closing date]={"<=$(=Today(2))"} and [Candidate Name]={"=Len(Trim([Candidate Name])) = 0"}>}Spec)

=Count(DISTINCT{$<[Closing date]={"<=$(=Today(2))"} and [Candidate Name]={"=IsNull([Candidate Name])"}>}Spec)

Re: Null in set analysis

Gysbert_Wassenaar — Wed, 18 Nov 2015 12:25:55 GMT

You can't select nulls. The best you can try is inversing a selection of anything not null:

=Count(DISTINCT{$<[Closing date]={"<=$(=Today(2))"} and [Candidate Name]=-{"*"}>}Spec).

But a better solution is to replace nulls with real values. See this document for more information: NULL handling in QlikView

Re: Null in set analysis

settu_periasamy — Wed, 18 Nov 2015 12:29:05 GMT

or May be

=Count(DISTINCT{$<[Closing date]={"<=$(=Today(2))"},[Candidate Name]-={"*"}>}Spec)

Re: Null in set analysis

Wed, 18 Nov 2015 13:04:58 GMT

I did try this code but the and operator make the code to have an error, and I tried to remove the and operator and put a coma separator,but is is returning wrong results, it return 0,i want to see specs that are not submitted and for me to see that it is when the candidate Name field is emply,so i am counting empty field on my spred sheet

Re: Null in set analysis

Wed, 18 Nov 2015 13:10:13 GMT

the - operator makes this code to have an error but when i remove it,it only counts the number of empty cell on candidate name from the spread sheet not where the dates is less than today