I have a fact table containing patient data. For example, patient is treated for Disease A , Disease B or combination AB. A patient might have multiple entries in a fact table among different Disease group. Now, when I sum on patient for an individual group I get the total from all three groups. How can I tell QV to do a distinct on a patient. If patient has already included in a count for one group DONT count it another time.
If you don't have disease as a dimension in your chart, count(distinct patient) will only count the patients once. If you DO have disease as a dimension in your chart, WHICH disease did you want to include the patient under if they're been treated for multiple diseases? QlikView isn't making the decision for you, which is why you'll see the patient counted under each disease. However, if you do a total for the diseases, it will then count correctly again (unless you select sum of rows).
John You understood the question right. I have disease as a dimesion in my table chart. I have given a group_code for a patient and disease where patient belongs to. If patient has code A for a particular date, and he occurs under code B on the same date I want to exclude one. I know its hard for you to understand. I will share the app soon.
How would you write the following SQL statement as a QV expression. My fact table is MONTH_PTNT_CNT which has data at regimen level(a lowest regimen level = RGMN_NBR). Another table is REGIMEN_GRP table which states what regimen belongs to which group. It is highly likely that a regimen can belong to more than one regimen_group Or you can say that some regimen_nbr will share parents. In SQL you can easily identify by using IN keyword as stated in the query.
select
sum(TOT_PTNT_CNT) from MONTH_PTNT_CNT where s_gid=21
and rgmn_nbr IN (select rgmn_nbr from REGIMEN_GRP where s_gid=21 and rgmn_grp_gid = 15)
