Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
jmalenfant
Partner - Contributor III
Partner - Contributor III

Display top N most frequent records in chart

Hi all - I'm trying to do something that I assume is a simple calculation that I'm struggling with for some time now.

Goal: display only the records that appear more than 2 times in a dataset.

Example of data:

TableIDICD-9 CodePatient IDVisit ID
12501A
22502B
32503C
4194D
54935E
64936F
71505G
94938H
104938I

I'd like to produce a list of all ICD-9 codes that appear more than 2 (or any #) of times in the data, including both the ICD-9 Code value and the count of how many times this unique code appeared

Example output

ICD-9 Code# of times the code was found in data (must be at least 2 times)
2503
4934

I've tried a few versions of this but can't seem to get it right

count( DISTINCT {<DX ={"=count(DX)>100"}>}DX), DX

If(Rank(Count(DISTINCT DX)) >100, Count(DISTINCT DX))

Thanks in advance!

4 Replies
sunny_talwar

Try this

Dimension

ICD-9 Code

Expression

Count({<[ICD-9 Code] = {"=Count(DX) >= 2"}>}DX)

MK_QSL
MVP
MVP

Create a Straight/Pivot Table


Dimension

[ICD-9 Code]

Expression

Count(Distinct{<[ICD-9 Code] = {"=COUNT(Distinct TableID) > 2"}>}TableID)

Or

Count({<[ICD-9 Code] = {"=COUNT([ICD-9 Code]) > 2"}>}[ICD-9 Code])

sunny_talwar

Here is a sample attached

Capture.PNG

jmalenfant
Partner - Contributor III
Partner - Contributor III
Author

Thanks so much - I knew it had to be something simple!