topic Re: Join tables multiple times in QlikView

Join tables multiple times

Margaret — Fri, 08 Jul 2016 16:52:48 GMT

Is it possible to join two Qlikview Tables multiple times?

I have a table with Hospital visits with various things that happen over the course of a visit. Often different Providers (Doctor, Nurse Practitioner, etc.) are associated with different aspects of the same visit. Can I join to the Hospitalist table multiple times? If so, how?

Re: Join tables multiple times

boorgura — Fri, 08 Jul 2016 17:21:00 GMT

Can you please share a sample of expected output data format?

Or do you expect the result to have multiple columns - like DS_Name, HP_Name, AD_Name

Re: Join tables multiple times

alexpanjhc — Fri, 08 Jul 2016 17:28:02 GMT

instead of joining many times (which I do not know what that means), I would create different tables like Hospotalists that joins different fields in the HospitalMedcine table. That will make it a preferable model for Qlikview. However, no one knows your data better than yourself. This is only my 2 cents if that makes sense.

Re: Join tables multiple times

Margaret — Fri, 08 Jul 2016 17:36:45 GMT

I want to be able to create sheet objects using the Hospitalist's (full)Name (not the abbreviated ID which is used in the DS_Dictator field and the other fields in Hospital Medicine that have red lines drawn next to them) that show the work that a specific Hospitalist does, without having to click on that person in multiple listboxes. That is, I don't want to have to filter more than once to see the work of one hospitalist. I also want to filter by GroupName, which I can't do unless I link to the Hospitalists table which contains GroupNames.

For example, I want to create a sheet with

a line graph showing percentage of Discharge Summaries dictated on or before the date of discharge over time
a line graph showing percentage of patients for whom an Advance Directive was ordered over time
a single listbox where a user can select one or more Hospitalist(s) that will make both above referenced line graphs display the data for only that/those Hospitalist(s)

Re: Join tables multiple times

Margaret — Fri, 08 Jul 2016 17:39:27 GMT

Maybe I should have specified that when I said joining multiple times, I meant I want those red lines I drew in the image to be joins in QlikView.

Are you saying I should write the script so that the Table Viewer shows multiple instances of the Hospitalist table, each joined once to the HospitalMedicine table? I considered this, but it sounds cumbersome and it prevents me from doing what I describe in my response to boorgura, above--I think.

Re: Join tables multiple times

rwunderlich — Fri, 08 Jul 2016 17:39:44 GMT

I would create a link table between the two tables that would associate a VistiID with multiple ProdivderIds.

Example attached.

-Rob

Re: Join tables multiple times

boorgura — Fri, 08 Jul 2016 17:43:46 GMT

Let me ask you this:

you one row (or one record) always have only 1 ID populated? If more than 1 is populated, will all of them be the same?

Re: Join tables multiple times

Margaret — Fri, 08 Jul 2016 17:47:56 GMT

One Visit Record in the HospitalMedicine Table will have more than one ProviderID, and those ProviderID's may be different (because different providers are associated with different aspects of the same visit).

Re: Join tables multiple times

Margaret — Fri, 08 Jul 2016 19:09:38 GMT

Hi, Rob. I read up on Link Tables, but I can't figure out what you mean. Could you please explain? I really appreciate your help.

Re: Join tables multiple times

rwunderlich — Fri, 08 Jul 2016 20:16:00 GMT

I've attached an example in my reply above. Download it and see if it makes sense to you.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Join tables multiple times

MarcoWedel — Sat, 09 Jul 2016 00:01:49 GMT

Hi,

to create a link table between your HospitalMedicine and Hospitalists table, you could also try with:

tabLink:

Crosstable (Role, HospID)

LOAD VisitID,

DS_Dictator,

HP_Dictator,

AD_Orderer,

Dis_Biller,

FUBiller

Resident HospitalMedicine;

Also change the DS_Dictator field name of your Hospitals table to "HospID".

Hope this helps

regards

Marco

Re: Join tables multiple times

Margaret — Tue, 12 Jul 2016 18:03:56 GMT

So it sounds like the literal answer to my question is "no"--you can't join tables in QlikView more than once?

Or you can, but it's a bad idea because...?

Re: Join tables multiple times

MarcoWedel — Tue, 12 Jul 2016 21:39:30 GMT

Yes, you can load the same Hospitalists table multiple times using different (role specific) table and field names.

Your specified function to be able to click on one person to see all corresponding visits regardless of the hospitalist role suggests another solution though. That's why you received solutions that implement your requirement.

Please close your thread, if your question is answered.

thanks

regards

Marco