Data Model and Export Data Help. For New Medical Research Application
I am currently developing a very exciting Medical Research Application however I am a little stuck on the best way to set up the data architecture to enable end users to use this information how they want to. I am keen to get this right and present Qlikview in a good light as if this is successful it is likely Qlikview will be used for further Medical Research linked to this project throughout the UK.
What do the end users want?
- Stage 1: to use Qlikview as a data repository where people can filter information and then export it to excel form which they will run statistical analysis.
What data are we working with? Essentially we have information from 7 different tables. Each of these tables has:
- Patient ID: however there may be more than one record for each patient ID per table
- Event Date
- Approx 50 fields of different tests/samples/diagnosis. Note: Each of these fields could be classed as a transaction and a dimension field as people will also want to use these as dimensions for filtering information
- Results for each of the above fields
The approaches tried so far and issues I have found (also see attached example documents):
- Example A: Load each table separately and link on Patient ID. Each field is potentially a dimension and transaction. The filtering works as required however problems arise when exporting the data.
o This is not a problem if I export one table at a time however if I want to combine fields from multiple tables and export this then records are duplicated
- Example B: Use Cross Tab load to create an Event Table and then load dimension tables
o Exporting of information works well and allows information to be combined from multiple tables, however the Events table shows all records where only one instance meets the criteria (see bookmark in example b).
I have attached a couple of simplified examples illustrating the different approaches I have tested to date. If you require any further information please let me know.
Any help or suggestions on what I can try would be much appreciated.