2 Fields from Fact Table refer to 1 field in Dimension Table
Hey! I am currently working on a problem I cannot find a real solution to.
I have one real big Fact Table and some Dimension Tables to refer to.
Basically my Fact table looks like this:
Patient ID
TCC 1
TCC 2
Many other columns
1
2
287
Other Data
2
271
271
Other Data
3
899
724
Other Data
(Patient ID from 1 to 50,000)
And a Dimension Table that looks like this:
TCC ID
TCC Text
TCC XXX
Many other Columns
1
Text
XXX
Other Data
2
Text
XXX
Other Data
(TCC ID from 1 to 10,000)
I need both, TCC 1 and TCC 2, to refer to the TCC ID. But I simply dont know how to. Later on Ive got a similar problem with other fields that refer to the same field in a dimension table.
Your TCC dimension is what is referred to as a "Role Playing" dimension., like multiple dates in a fact table (Ex. Quote Date, Order Date, Ship Date, Payment Date, etc) You have 2 choices:
1. in qlik script, create 2 dimensions TCC1 and TCC2, This allow users to make independent selection on either. This is the simplest method, but requires 2 selection filters. The disadvantage is that you cannot capitalize on the use of set analysis.
2. Create a single TCC dimension, and a "link table", which as a key of : autonumber(Fact.tcc1 & '|' & Fact.tcc2) as TccLinkKey.
Its final structure will be:
TccLinkKey
TCC Code
TCC Description
TCC1Flag (1=true, 0=false)
TCC2Flag (1=true, 0=false)
The above single dimension allows you to have a single dimension for users to filter on. However, you will need to use set analysis for your measures. For me this is a blessing, for some it is a curse.
Ex: Count of Patients (TCC1) = Count({$<TCC1Flag={1}>}Distinct PatientID)
Ex: Count of Patients (TCC2) = Count({$<TCC2Flag={1}>}Distinct PatientID)
I do this all the time with a master calendfar and a master calendar link table
Why dont you move the tcc's into another table? which would have 2 rows i.e. new table would be patientid tccid and 1 patient would have 2 rows here Makes it slightly snowflakey but reasonable compromise
Your TCC dimension is what is referred to as a "Role Playing" dimension., like multiple dates in a fact table (Ex. Quote Date, Order Date, Ship Date, Payment Date, etc) You have 2 choices:
1. in qlik script, create 2 dimensions TCC1 and TCC2, This allow users to make independent selection on either. This is the simplest method, but requires 2 selection filters. The disadvantage is that you cannot capitalize on the use of set analysis.
2. Create a single TCC dimension, and a "link table", which as a key of : autonumber(Fact.tcc1 & '|' & Fact.tcc2) as TccLinkKey.
Its final structure will be:
TccLinkKey
TCC Code
TCC Description
TCC1Flag (1=true, 0=false)
TCC2Flag (1=true, 0=false)
The above single dimension allows you to have a single dimension for users to filter on. However, you will need to use set analysis for your measures. For me this is a blessing, for some it is a curse.
Ex: Count of Patients (TCC1) = Count({$<TCC1Flag={1}>}Distinct PatientID)
Ex: Count of Patients (TCC2) = Count({$<TCC2Flag={1}>}Distinct PatientID)
I do this all the time with a master calendfar and a master calendar link table
