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: 
Anonymous
Not applicable

Straight Table - need set analysis for summary columns


Greetings,

I have a fact table that has 4 fields.  Diagnosis Code, Diagnosis Code 2, Diagnosis Code 3 and Diagnosis Code 4.  See attached example picture. I would like to create a straight table that gives me the sum of the number of times each code is used in each column.  In my example there are 59805 rows and code 7291 is used 74 times as the Primary Diagnosis.  If you look at the three boxes below in the picture, you can see that 7291 was used 356 as a secondary diagnosis, 321 as a third diagnosis and 375 as a 4th diagnosis.  I would like my straight table to shows those number across the row and allow me to get a total number of conditions where a patient was diagnosis with the code.

My dimension is Diagnosis Code and the Count of Primary is a simple =count([Diagnosis Code])

For example, the first row of the table might have

Diagnosis Code 7291, Secondary Code 4019, Third Code as 2724 and Forth Code as 2449

The code numbers will always be unique on the same row.

My brain can't come up with the logic to complete the summary formulas for Diag Code 2, 3 and 4.

Please help if you have the answer.

Thanks,

Mike

Message was edited by: Mike Czerwonky Here is a copy of my QVW sample. This should give you what you need to see what I am looking to do !

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Here is a solution to my problem.  Many thanks to @Rob Wunderlich for providing this example and guiding me towards solving my challenge.  In this case, a resident load was used to create an auxiliary table.  This table took the values from my four columns and combined them into one called Diagnosis Code.  The load process also created a position column with the appropriate column 1 to 4.  Then, a sum formula could be used to determine position.

Using this logic, I was able to plug this code method back into my app with 23 million rows and meet my end user requirements.

Hope this follow up helps someone else. !

View solution in original post

5 Replies
mrossoit
Creator II
Creator II

Hi,

maybe your data model has a problem.

You should join Diagnosis Code with DiagCd2, DiagCd3 and DiagCd4.

Upload your qvw, please

Regards

MR

santhoo_san
Partner - Creator II
Partner - Creator II

you can try below expression with the same dimension

Expression1

count({<DiagCd2-={''}>}[DiagCd2)

Expression2

count({<DiagCd3-={''}>}[DiagCd3)

Expression3

count({<DiagCd4-={''}>}[DiagCd4)

ramoncova06
Specialist III
Specialist III

have you tried

count(Diag)

+

count(Diag2)

+

count(Diag3)

+

count(Diag4)

Anonymous
Not applicable
Author

If you select any of the Diagnosis Codes in the table, it should reduce to one row with the summary for each of the columns.  However, selecting the primary will always equal zero for the 2,3 and 4 columns as there can be no duplicates.  The set analysis needs to consider the whole data set while matching the primary dimension for columns for 2, 3 and 4

Anonymous
Not applicable
Author

Here is a solution to my problem.  Many thanks to @Rob Wunderlich for providing this example and guiding me towards solving my challenge.  In this case, a resident load was used to create an auxiliary table.  This table took the values from my four columns and combined them into one called Diagnosis Code.  The load process also created a position column with the appropriate column 1 to 4.  Then, a sum formula could be used to determine position.

Using this logic, I was able to plug this code method back into my app with 23 million rows and meet my end user requirements.

Hope this follow up helps someone else. !