Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Problem to join two tables

Hi everyone

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

Table 1    

Hospital NameADT Admission YearADT Admission MonthADT Admission DayPatient Full NameP National CodeComposition ID
H12015112Arash11111
H12015112Arash11112
H12015112Sandy22223
H12015112Sandy22224
H12015112Sandy55555

And


Table 2      

Hospital NameYearMonthDayAccident Price
H12015112

2000000

How can i join these two tables?

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

1 Solution

Accepted Solutions
ahaahaaha
Partner - Master
Partner - Master

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

1.jpg

Regards,

Andrey

View solution in original post

7 Replies
ahaahaaha
Partner - Master
Partner - Master

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
Not applicable
Author

Hi Andrey

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

ahaahaaha
Partner - Master
Partner - Master

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

1.jpg

Regards,

Andrey

Anonymous
Not applicable
Author

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
Partner - Master
Partner - Master

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
Not applicable
Author

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
Partner - Master
Partner - Master

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