Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

lubicz99
Valued Contributor

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
lubicz99
Valued Contributor

Re: Straight Table - need set analysis for summary columns

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. !

5 Replies
mrossoit
Contributor II

Re: Straight Table - need set analysis for summary columns

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
Contributor II

Re: Straight Table - need set analysis for summary columns

you can try below expression with the same dimension

Expression1

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

Expression2

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

Expression3

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

ramoncova06
Valued Contributor III

Re: Straight Table - need set analysis for summary columns

have you tried

count(Diag)

+

count(Diag2)

+

count(Diag3)

+

count(Diag4)

lubicz99
Valued Contributor

Re: Straight Table - need set analysis for summary columns

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

lubicz99
Valued Contributor

Re: Straight Table - need set analysis for summary columns

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. !

Community Browser