One Dimension Table for Multiple Fact Fields

[#1]

Is there a good way of loading one dimension table and associating it to multiple fact fields?

As an example, let's assume you have a dimension table "Diagnosis" with three fields: [Diagnosis Code], [Unstripped Diagnosis Code], and [Diagnosis Code Description].  Now let's assume you have a fact table "Transactions" with the following fields:

• Patient ID
• Patient Name
• Diagnosis1
• Diagnosis2
• Diagnosis3

How can I load/script the tables to associate [Diagnosis Code] to Diagnosis1, Diagnosis2, and Diagnosis3?  Ideally I should be able to create a current selection on [Diagnosis Code Description] and it display the associated patients that have that corresponding diagnosis code in Diagnosis 1,2,or3.

Thanks for helping the new guy!

[johnw · Accepted solution]

Create three tables instead of two, with this sort of layout:

Patient:
Patient ID, Patient Name  

Patient Diagnosis:
Patient ID, Diagnosis Sequence, Diagnosis Code   

Diagnosis:
Diagnosis Code, Unstripped Diagnosis Code, Diagnosis Code Description

Look up "crosstable load" for how to easily convert the diagnosis columns to rows in the new table.

[gussfish]

Hi,

taking your example just as you've described it, you'd need to create a dimension table for each of the 3 diagnoses columns, because QlikView by default matches columns by name i.e. if you have Diagnosis1 as a column, you need to have a column with that same name in whatever other table you want it to be associated with.

Much better will be to unpivot your fact table so that its colums are something like

• Patient ID
• Patient Name
• Diagnosis Serial Number // this has value 1, 2 or 3
• Unstripped Diagnosis Code // I'm assuming this is the actual unique identifier of a diagnosis; adjust this if I'm wrong!

Hence, your fact table will have 1 record per patient diagnosis, instead of 1 record per patient.  (As an aside, this doesn't look very efficient, since Patient Name seems to be duplicated unnecessarily, and indeed you could normalise this further; however, QlikView is in fact very efficient at storing such duplicates, so that it isn't an issue, and QlikView performance is better when the number of associations from one end of your data model to the other is shorter).

To convert your original fact table into this form, you can use a CROSSTABLE LOAD statement - the "Table Files..." script wizard will help you construct this when you press the "Crosstable..." button on the 3 page of the wizard.  When it asks, your 1st two columns are your qualifier columns, Diagnosis Serial Number will be your Attribute Field, and Unstripped Diagnosis Code your Data Field.

Angus.