Find the distinct count using count if

[sudms]

Hello,

I am trying to get the total distinct count from below table.

Category	SubCat	ID
ACS	ACS	10
ACS	Chat	9
ACS	Email	6
ACS	Email	12
ACS	Email	12
ACS	Email	12
ACS	Email	12
ACS		11
Chat	Chat	1
Chat	Chat	4
Chat	Email	1
Chat	Email	1
Chat	Other	5
Chat		2
ACS	Other	12

Here if you see I have two conditions like

1> SubCat should not be null

2> Category and SubCat should not match

With this I want the single value in my KPI of distinct count 5. I tried with below query but it gives 6 for the Aggr.

Sum(Aggr(IF(Len(SubCat)>0 and SubCat<>Category, count(distinct ID)),SubCat,Category))

Any solution to get the answer.  

[Kushal_Chawda · Accepted solution]

@sudms  best way to do it is create a flag in script for the required conditions and use that flag in expression to get the count

Data:
LOAD Category,
            SubCat,
            if(len(trim(SubCat))>0 and SubCat<>Category,1,0) as Flag,
            ID
FROM Table;

Now, create a expression like below

=Count({<Flag={1}>}DISTINCT ID)