Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
This might be a confusing one to explain but I'll try my best.
I have three different data sources that I need to link together to report on. They are coming from different databases in a hospital and all have the unique patient identifier (MRN) so this is the only way that I know I can link the tables together. Each of the databases contains different information on an event that could happen to a patient (e.g. cardiac arrest, patient alarms etc).
I also want to be able to filter by date and show what has happened on certain dates to give me totals of how many of these events occurred on a certain day. This is where things get difficult. I have created a 'DateLink' table using the date/time stamp for each table and then linked the DateKey to a master calendar.
Now, when I filter using this DateKey field, I get way more than just the date that I am filtering on (even when I use the DateType field as a filter that I have created in the DateLink table). I believe this has something to do with the MRN being a common field. Qlik is seeing that there are more 'things' happening with that MRN in other tables then, coming back and grabbing those 'dates' from the initial table.
This is hard to explain. I have provided screenshots of the Table Viewer and a testing page I have created with the dates from each table along with the DateKey and DateType (with a single DateKey and DateType filter highlighted). Unfortunately, can't share the whole Qlikview file due to the nature of the information. Any information or help on how to accurately filter the dates would be great.
Problem is with the link table try like this
Table1:
LOAD
MRN as Table1_MRN,
Date as Table1_Date,
MRN&'_'&Date as Table1_Key
...
from table1
Table2:
LOAD
MRN as Table2_MRN,
Date as Table2_Date,
MRN&'_'&Date as Table1_Key
.....
from table2
LINK_TABLE:
LOAD
'Tabel1' as Source,
Table1_MRN as MRN ,
Table1_Date as Date,
Table1_Key
resident
Table1;
concatenate
LOAD
'Tabel2' as Source,
Table2_MRN as MRN ,
Table2_Date as Date,
Table2_Key
resident
Table1;
Calender:
LOAD
Date,,...
Problem is with the link table try like this
Table1:
LOAD
MRN as Table1_MRN,
Date as Table1_Date,
MRN&'_'&Date as Table1_Key
...
from table1
Table2:
LOAD
MRN as Table2_MRN,
Date as Table2_Date,
MRN&'_'&Date as Table1_Key
.....
from table2
LINK_TABLE:
LOAD
'Tabel1' as Source,
Table1_MRN as MRN ,
Table1_Date as Date,
Table1_Key
resident
Table1;
concatenate
LOAD
'Tabel2' as Source,
Table2_MRN as MRN ,
Table2_Date as Date,
Table2_Key
resident
Table1;
Calender:
LOAD
Date,,...
hi,
while creating link table do following things:
1.find unique key in your fact tables
2.with this key and required dimension create link table.
3. every fact table should link to link table with the respective key.
note: please conform that the key your creating is unique
this will resolve your issue. if still you are facing same issue give us scrambled data anything else for reference.
thanks
mayuresh
Hi Avinash,
Thank you.
This does seem to be working but it is giving me a few synthetic keys for the 'Table1_Key' fields. I'm not sure what impact this will have on the rest of my data yet though.
Cheers,
Glen
Do you have the common key across the tables ? then I think that is the issue we need to qualify the fields ..share the sample files will check and suggest