topic Re: Problem to join two tables in QlikView

Problem to join two tables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone

I Want to join these two tables but unfortunately my Accident Price repeated wrongly

Table 1

Hospital Name	ADT Admission Year	ADT Admission Month	ADT Admission Day	Patient Full Name	P National Code	Composition ID
H1	2015	1	12	Arash	1111	1
H1	2015	1	12	Arash	1111	2
H1	2015	1	12	Sandy	2222	3
H1	2015	1	12	Sandy	2222	4
H1	2015	1	12	Sandy	5555	5

And

Table 2

Hospital Name	Year	Month	Day	Accident Price
H1	2015	1	12	2000000

How can i join these two tables?

I would appreciate if any one can help me in this Discussion .

Re: Problem to join two tables

ahaahaaha — Sun, 05 Mar 2017 06:24:29 GMT

Hi Elham,

In your data value field [Hospital Name] in Table 1 and Table 2 are different. It is right? In what fields do you associate the two tables?

Regards,

Andrey

Re: Problem to join two tables

Anonymous — Sun, 05 Mar 2017 06:30:46 GMT

Hi Andrey

hospital name and the date(year,month and day) are the same in two tables

Re: Problem to join two tables

ahaahaaha — Sun, 05 Mar 2017 06:56:44 GMT

I have created a composite key values of the fields [Hospital Name], [ADT Admission Year], [ADT Admission Month] and [ADT Admission Day] and would rename field [Hospital Name] in Table 2.

For example like this

Directory;

Table1:

LOAD [Hospital Name],

[ADT Admission Year],

[ADT Admission Month],

[ADT Admission Day],

[Patient Full Name],

[P National Code],

[Composition ID],

[Hospital Name]&'|'&[ADT Admission Year]&'|'&[ADT Admission Month]&'|'&[ADT Admission Day] as %Key

FROM

Table1.xlsx

(ooxml, embedded labels, table is Sheet1);

Directory;

Table2:

LOAD [Hospital Name] as [Hospital Name Price],

Year,

Month,

Day,

[Accident Price],

[Hospital Name]&'|'&Year&'|'&Month&'|'&Day as %Key

FROM

Table2.xlsx

(ooxml, embedded labels, table is Sheet1);

Result

Regards,

Andrey

Re: Problem to join two tables

Anonymous — Sun, 05 Mar 2017 07:24:36 GMT

Thank you Andrey for implementation of the example

these fields "[Hospital Name]&'|'&[ADT Admission Year]&'|'&[ADT Admission Month]&'|'&[ADT Admission Day]" aren't the key, Table 1 have same hospital name with a same date more than 1 row, so i think we need more fields for key

And why you didn't join hospital name, year , month and day with hospital name, [ADT Admission Year], [ADT Admission Month], [ADT Admission Day], because they are the same, When i filter a same date in dashboard it must be act on Accident Price too.

Re: Problem to join two tables

ahaahaaha — Sun, 05 Mar 2017 07:40:16 GMT

The idea of in general: a tables, ideally well bind one key field. That's why I proposed the creation of an additional key field. This will allow you to select the data in the charts including the Accident Price too.

Re: Problem to join two tables

Anonymous — Sun, 05 Mar 2017 09:23:24 GMT

Dear Andrey

The key in the table is not combination of [Hospital Name]&'|'&[ADT Admission Year]&'|'&[ADT Admission Month]&'|'&[ADT Admission Day]

Re: Problem to join two tables

ahaahaaha — Sun, 05 Mar 2017 13:52:28 GMT

Hi Elham,

Maybe I did not understand everything correctly, but one of the tables is not the appropriate data fields [Patient Full Name], [P National Code] and [Composition ID]. How can we include these fields for connection of tables?

Regards,

Andrey