Problem to join two tables

[Anonymous]

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 .

[ahaahaaha]

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

[Anonymous]

Hi Andrey

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

[ahaahaaha · Accepted solution]

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

[Anonymous]

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.

[ahaahaaha]

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.

[Anonymous]

Dear Andrey

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

The key is combination of [Hospital Name]&'|'&[ADT Admission Year]&'|'&[ADT Admission Month]&'|'&[ADT Admission Day]&'|'&[Patient Full Name]&'|'&[P National Code]&'|'&[Composition ID]

[ahaahaaha]

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