topic Re: If Null, Count Distinct in QlikView

If Null, Count Distinct

ksomosera10 — Mon, 18 Sep 2017 05:43:20 GMT

Hi,

I have this expression but gives me a "No Data to Display" can you help me out on this? I'm not familiar with set analysis.

=If(Not Isnull([Name]), Count (DISTINCT [Skill]), Null())

All I want is this:

If Name is not null then count their distinct skills. If the condition is not satisfied then return null.

I hope you folk can help me out here. Thanks!

Re: If Null, Count Distinct

ashwinishinde — Mon, 18 Sep 2017 06:26:05 GMT

Hi,

Try This: In Script: If (Isnull([Name]), Null(),[Name]) as Emp_Name

On UI : Count (DISTINCT [Skill])

Thanks & Regards,

Ashwini

Re: If Null, Count Distinct

prma7799 — Mon, 18 Sep 2017 06:30:17 GMT

Try like this

=If(len(trim([Name]))>0, Count (DISTINCT [Skill]), Null())

Re: If Null, Count Distinct

ksomosera10 — Mon, 18 Sep 2017 06:32:27 GMT

I still get No Data to Display. I think Set-analysis is the solution to this.

Re: If Null, Count Distinct

prma7799 — Mon, 18 Sep 2017 06:33:56 GMT

Could you please share sample app

Re: If Null, Count Distinct

tresB — Mon, 18 Sep 2017 06:34:24 GMT

Try like:

=Count(DISTINCT If(len(trim([Name]))>0, [Skill]))

Re: If Null, Count Distinct

prma7799 — Mon, 18 Sep 2017 06:37:01 GMT

please try like this

=If(isnull([Name]), Count (DISTINCT [Skill]), Null())

Re: If Null, Count Distinct

ksomosera10 — Mon, 18 Sep 2017 06:38:11 GMT

Thanks Tresesco for answering, but the result are the same. I get the count of all skills even when the name is null.

Re: If Null, Count Distinct

ksomosera10 — Mon, 18 Sep 2017 06:40:21 GMT

I still get the No data to Display

Re: If Null, Count Distinct

prma7799 — Mon, 18 Sep 2017 06:45:59 GMT

Please share some sample app

Re: If Null, Count Distinct

tresB — Mon, 18 Sep 2017 08:55:38 GMT

Check if this works:

=Count(DISTINCT {<[Skill]={"=len(trim([Name]))>0"}>} [Skill]))

Re: If Null, Count Distinct

antoniotiman — Mon, 18 Sep 2017 09:27:52 GMT

Hi Kevin,

try

=Count({<Name={*}>} DISTINCT Skill)

Regards,

Antonio

Re: If Null, Count Distinct

Kushal_Chawda — Mon, 18 Sep 2017 09:50:44 GMT

I would suggest to create the Flag in script,

LOAD *,

if(len(trim(Name))>0,1,0) as NameFlag

FROM table

now you can use the set analysis like below

=count(distinct{<NameFlag={1}>}Skill)

or set analysis

=count(DISTINCT{<Name ={*}>}Skill)

Re: If Null, Count Distinct

Chanty4u — Mon, 18 Sep 2017 09:52:56 GMT

try this

=count( if(IsNull(Field),Dim))