topic Re: Straight Table - need set analysis for summary columns in QlikView

Straight Table - need set analysis for summary columns

Anonymous — Mon, 11 May 2015 13:53:23 GMT

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 !

Re: Straight Table - need set analysis for summary columns

mrossoit — Mon, 11 May 2015 14:03:46 GMT

Hi,

maybe your data model has a problem.

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

Upload your qvw, please

Regards

Re: Straight Table - need set analysis for summary columns

santhoo_san — Mon, 11 May 2015 14:10:07 GMT

you can try below expression with the same dimension

Expression1

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

Expression2

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

Expression3

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

Re: Straight Table - need set analysis for summary columns

ramoncova06 — Mon, 11 May 2015 14:46:04 GMT

have you tried

count(Diag)

count(Diag2)

count(Diag3)

count(Diag4)

Re: Straight Table - need set analysis for summary columns

Anonymous — Mon, 11 May 2015 14:50:45 GMT

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

Re: Straight Table - need set analysis for summary columns

Anonymous — Thu, 14 May 2015 12:34:30 GMT

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