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:
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.
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
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.