Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hope you can help me out.
Thanks!
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
Dave
Hi, You need an interval match to get the relation between begin and end ID with you dimension table
How would I do that? I don't really have intervalls, just two field pointing at the same field in the other 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
Dave