topic Re: Count Values Based on Duplicate Values in App Development

Count Values Based on Duplicate Values

robertsb41 — Sat, 16 Nov 2024 01:53:31 GMT

I have two columns, Contract ID & Bid Rank. I want to count the number of bid rank for each unique contract ID. So for 180119-B03 the result would be 7 and for 180119-C01 the result would be 6. Thanks!

Contract ID	Bid Rank
180119-B03	1
180119-B03	2
180119-B03	3
180119-B03	4
180119-B03	5
180119-B03	6
180119-B03	7
180119-C01	1
180119-C01	2
180119-C01	3
180119-C01	4
180119-C01	5
180119-C01	6

Re: Count Values Based on Duplicate Values

Taoufiq_Zarra — Wed, 15 Jul 2020 13:41:59 GMT

if you want to keep Table detail :

Solution :

Data: LOAD * INLINE [ Contract ID, Bid Rank 180119-B03, 1 180119-B03, 2 180119-B03, 3 180119-B03, 4 180119-B03, 5 180119-B03, 6 180119-B03, 7 180119-C01, 1 180119-C01, 2 180119-C01, 3 180119-C01, 4 180119-C01, 5 180119-C01, 6 ]; left join load [Contract ID], count([Bid Rank]) as Count_BidRank resident Data group by [Contract ID];

output :

or without detail :

Data: LOAD * INLINE [ Contract ID, Bid Rank 180119-B03, 1 180119-B03, 2 180119-B03, 3 180119-B03, 4 180119-B03, 5 180119-B03, 6 180119-B03, 7 180119-C01, 1 180119-C01, 2 180119-C01, 3 180119-C01, 4 180119-C01, 5 180119-C01, 6 ]; output: noconcatenate load [Contract ID], count([Bid Rank]) as Count_BidRank resident Data group by [Contract ID]; drop table Data;

output :

Re: Count Values Based on Duplicate Values

robertsb41 — Wed, 15 Jul 2020 13:47:13 GMT

Thank you for your reply but I am just looking for an expression for a KPI. I should have been more clear. Is that possible?

Re: Count Values Based on Duplicate Values

Taoufiq_Zarra — Wed, 15 Jul 2020 13:52:06 GMT

you can add

Contract ID as dimension and =count([Bid Rank]) as Expression

output :

or kpi for each Contract ID

=count({<[Contract ID]={'180119-B03'}>}[Bid Rank])

output :