Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Malle
New Contributor

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 IDTCC 1TCC 2Many other columns
12287Other Data
2 271271Other Data
3899724Other Data


(Patient ID from 1 to 50,000)

And a Dimension Table that looks like this:

TCC IDTCC TextTCC XXXMany other Columns
1TextXXXOther Data
2TextXXXOther 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! 

1 Solution

Accepted Solutions
dadumas
Contributor II

Re: 2 Fields from Fact Table refer to 1 field in 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

Dave

5 Replies
albert_guito
Contributor II

Re: 2 Fields from Fact Table refer to 1 field in Dimension Table

Hi, You need an interval match to get the relation between begin and end ID with you dimension table

Ag+
Malle
New Contributor

Re: 2 Fields from Fact Table refer to 1 field in 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.

Partner
Partner

Re: 2 Fields from Fact Table refer to 1 field in Dimension 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
dadumas
Contributor II

Re: 2 Fields from Fact Table refer to 1 field in 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

Dave

Highlighted
dadumas
Contributor II

Re: 2 Fields from Fact Table refer to 1 field in Dimension Table

Did not explain well enough. You will also need the TCCDIM dimension:
TCC Code
TCC Description
....

TCC Description would not exist in the link table.