Create three tables instead of two, with this sort of layout:
Patient ID, Patient Name
Patient ID, Diagnosis Sequence, Diagnosis Code
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.
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.