Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robertsb41
Contributor III
Contributor III

Count Values Based on Duplicate Values

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 IDBid Rank
180119-B031
180119-B032
180119-B033
180119-B034
180119-B035
180119-B036
180119-B037
180119-C011
180119-C012
180119-C013
180119-C014
180119-C015
180119-C016
Labels (1)
1 Solution

Accepted Solutions
Taoufiq_Zarra

you can add

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

output :

Capture.PNG

or kpi for each Contract ID

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

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

3 Replies
Taoufiq_Zarra

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 :

Capture.PNG

 

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
robertsb41
Contributor III
Contributor III
Author

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?

Taoufiq_Zarra

you can add

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

output :

Capture.PNG

or kpi for each Contract ID

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

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉