topic Re: Set analysis : Count empty values in one column in App Development

Set analysis : Count empty values in one column

ClementByr — Sat, 16 Nov 2024 04:38:21 GMT

Hello,

I am trying to count how many empty values there in a single column. Here is a data sample :

Machine_ID	Date	Num_Incident
1	2016	101452400
1	2016	101452406
1	2016	101452000
1	2016	101452447
1		101452451
1	2016	101452452
1	2016	101452496
1	2017	101452000
1	2017	101452591
1		101452597
1	2018	101452601
1	2018	101452614
1	2019	101452621
2	2017	101452000
2	2017	101452682
2	2017	101452690
2	2017	101452829
2		101452835
2	2017	101452842
2	2017	101452843
2	2018	101452000
2	2018	101452907
2	2019	101452911
2	2019	101452915
2	2019	101452916
2	2019	101452918
2		101452000
2	2019	101452922
2	2019	101452923
2	2019	101452924
2	2019	101452928
3	2018	101452940
3	2018	101452941
3	2018	101452944
3		101452000
3	2018	101452961
3	2018	101453035
3	2018	101453000
3	2018	101453042
3	2019	101453048

I want to count all the null value in the Date column using set analysis which is supposed to be 5 null values. So far, I have tried these formula without success:

Count({<Machine_ID={'=Len(Trim(Date))=0'}>} Date)

Count({<Date={'=Len(Trim(Date))=0'}>} Date)

Count({<'=Len(Trim(Date))=0'>} Date)

Can someone help me figure out a solution, please ?

Best regards,

Clément Boyer.

Re: Set analysis : Count empty values in one column

sunny_talwar — Wed, 18 Sep 2019 15:05:25 GMT

Have you tried NullCount() function?

NullCount(Date)

Re: Set analysis : Count empty values in one column

Anil_Babu_Samineni — Wed, 18 Sep 2019 15:06:17 GMT

Try one of the below

Count(If(IsNull(Date), Date))

Count({<Date={"=Len(Date)=0"}>} Date)

Count({<Date-={"=Len(Date)>0"}>} Date)

NullCount(Date)

Re: Set analysis : Count empty values in one column

Vegar — Wed, 18 Sep 2019 15:07:52 GMT

If you are interested of counting nulls then you should look into the NullCount() function.

For chart:

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/CounterAggregationFunctions/nullcount.htm

For script:

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterAggregationFunctions/NullCount.htm

Re: Set analysis : Count empty values in one column

Vegar — Wed, 18 Sep 2019 15:12:19 GMT

Wow all three with the same advise within a couple of minutes @sunny_talwar @Anil_Babu_Samineni

😄😄😄

Re: Set analysis : Count empty values in one column

ClementByr — Thu, 19 Sep 2019 15:01:34 GMT

Thank you Vegar, Anil and Sunny,

Following your suggestion, I tried to calculate the number of null values in all the table. So I want to do an addition with the nullcount Machine_ID, Date, Num_Incident.

But if I do the formula : NullCount(Machine_ID) + NullCount(Num_Incident), I get the value of 2 where I am supposed to find 0 null cells. Can you please explain why ?

Best regards.

Re: Set analysis : Count empty values in one column

sunny_talwar — Thu, 19 Sep 2019 15:43:33 GMT

May be they are not null and just while spaces? Difficult to know without playing around with it