topic Re: Linking tables - common field and dates in QlikView

Linking tables - common field and dates

Anonymous — Sun, 06 Aug 2017 22:52:52 GMT

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.

Re: Linking tables - common field and dates

avinashelite — Mon, 07 Aug 2017 07:23:50 GMT

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

Re: Linking tables - common field and dates

mayuresh_d — Mon, 07 Aug 2017 08:45:25 GMT

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

Re: Linking tables - common field and dates

Anonymous — Tue, 08 Aug 2017 23:11:48 GMT

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

Re: Linking tables - common field and dates

avinashelite — Wed, 09 Aug 2017 13:05:33 GMT

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