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

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

Malle — Sat, 16 Nov 2024 21:38:02 GMT

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!

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

albert_guito — Wed, 09 Jan 2019 11:37:54 GMT

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

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

Malle — Wed, 09 Jan 2019 13:11:18 GMT

How would I do that? I don't really have intervalls, just two field pointing at the same field in the other table.

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

dplr-rn — Wed, 09 Jan 2019 14:26:35 GMT

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

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

dadumas — Wed, 09 Jan 2019 17:26:44 GMT

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

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

dadumas — Wed, 09 Jan 2019 17:30:50 GMT

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.