Finding Count of Field against another field distinct values(Ignoring duplicates)

[sharathkolluru1803]

Hi Experts,

I want your help in finding the distinct number(count) of IDs where table is having 2 coloumns ID and Order,
1)Order values considered for distinct ID if those are different.
2)If the ID and Order combination is single value needs to be ignored
3)If the ID and Order combination repeats then those needs to be ignored.

ID                  Order
ghi123         449
ghi123         393
lmn123       543
xyz123         999
xyz123         999
xyz123         999
pqr123        678
pqr123        445
pqr123        877
abc123       436
abc123       436

After applying above conditions ID count should be 2 (ghi123, pqr123) as the values are differed.

I tried with Chart expression "Sum(Aggr(Count(distinct Order)>1,ID))" like this but result are not correct.

Please do help me to solve this issue? Thanks a ton in advance.

[ajaykakkar93]

Hi,

Please check the following script & output

Output:

ID	Count(Distinct key)
pqr123	3
ghi123	2
abc123	1
lmn123	1
xyz123	1

Script : 

d:
load * Inline [
ID,Order
ghi123,449
ghi123,393
lmn123,543
xyz123,999
xyz123,999
xyz123,999
pqr123,678
pqr123,445
pqr123,877
abc123,436
abc123,436
];

final:
load 
ID&Order as key,
ID,
Order
Resident d;
drop table d;
exit script;

[sharathkolluru1803]

Thank you Ajay for quick reply.

But my Desired result is different, I need count of distinct key(ID)s where the values are not repeated  distinct only. Like key ‘pqr123’ has 3 different values so count of key(ID) is 1 and ‘ghi123’ has 2 different values so count of key is 1. So total count of distinct ID count is 2. Remaining all keys/ID values are either single or repeated with same values. So those needs to ignored in calculation.

[ajaykakkar93]

if(Count(Distinct key)>1,Count(Distinct key))

[ajaykakkar93]

Then you will get 

pqr123 & ghi123

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT {<ID = {"=Count(DISTINCT Order) > 1"}>} ID)

[sharathkolluru1803]

Thank You So much Sunny, You made my day.

[sharathkolluru1803]

Hi Sunny

I have tried like this, "Sum(Aggr(Count(distinct Order)>1,ID))" but result is correct but coming in negative. Could you please help me in understanding of problem for the same.

[sharathkolluru1803]

It is giving me total distinct count, but I need distinct count respective to ID. Thanks Ajay for your inputs.

[ajaykakkar93]

anytime, thank you