Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

How can i Left Join() multiple tables?

Hi guys,

i have four tables: A, B and C.

I need to bring all the fields of table C in table B and all the fields of table B in table A.

What's the right expression to do this?

Thanks,

Mattia

Labels (2)
10 Replies
Highlighted
Contributor III
Contributor III

TableA:

load * from tableA;

left join

load * from TableB;

left join

load * from TableC;

 

Highlighted
Partner
Partner

You need to make sure that the keys columns having the same name, i.e. if you want to join table A with table B using the column "EmployeeNo", this column called EmpNo in table A but it's called EmployeeNo in table B, you need to give alias to the column to have same name (case sensitive) then use the "Left Join" between the tables.

TableABC:

Load colum1, column2 as Key1

from TableA;

Left Join (TableABC)

Load colum3, column4, column5 as Key1, column6 as Key2

from TableB;

Left Join (TableABC)

Load colum7, column8, column9 as Key2

from TableC;

Yousef Amarneh
Highlighted
Creator II
Creator II

Thanks for the replies.

Yousef, here is attached a sample data file.

Can you help me to insert the script?

I've tried but something don't work.

Thanks,

Mattia

Highlighted
Partner
Partner

Hi Mattia,

I can also help with this.. please paste your script here instead of the qvw.

Highlighted
Partner
Partner

Use the below

TableABC:
LOAD * INLINE [
COUNT, CODE, NUM
00000001, 897, 1
00000001, 576, 2
00000001, 253, 3
00000002, 897, 1
00000002, 543, 2
00000003, 923, 1
];

Left Join (TableABC)
LOAD * INLINE [
COUNT, PROG, COM
0000001, 1, 999
0000001, 2, 999
0000002, 1, 555
0000003, 1, 222
];

Left Join (TableABC)
LOAD * INLINE [
COM, ASL, DIS
999, 030, LOM
555, 999
222, 010
];

 

and you will get the data shown in the attached

 

Yousef Amarneh
Highlighted
Creator II
Creator II

Hi Manishnarang,

TableC:
LOAD * INLINE [
COM, ASL, DIS
999, 030, LOM
555, 999
222, 010
];

TableB:
LOAD * INLINE [
COUNT, PROG, COM
0000001, 1, 999
0000001, 2, 999
0000002, 1, 555
0000003, 1, 222
];

TableA:
LOAD * INLINE [
COUNT, CODE, NUM
00000001, 897, 1
00000001, 576, 2
00000001, 253, 3
00000002, 897, 1
00000002, 543, 2
00000003, 923, 1
];

Highlighted

what is your expected output???

your joining field don't have unique value so it will result into one to many relation.

Please check your data.

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Creator II
Creator II

Hi Yousef,

i can't see the attachment.

Error file system (-2147219196)

Highlighted
Creator II
Creator II

Hi guys,

thanks to all for the replies and the suggests.

This is a part of a more complex project.

My goal is to compare two data flows:

1) FLOW1: tables TES, DET, COM and TAR

2) FLOW2: tables AMB1, AMB2, COM and TAR

The key fields betweet the 4 tables (TES/DET and AMB1/AMB2) are the same: COUNT and PRES

So, i want this: all the fields of tables COM and TAR both in tables TES and AMB2.

But, in the 1st flow, the table COM have a key field with the table TES, while the table TAR have a key field with the table DET that have a key field with table TES.

In the 2nd flow, the table TAR have a key field with the table AMB2, while the table COM have a key field with the table AMB1 that have a key field with table AMB2.

To have a better understand of what i need, i can attach a sample data file with all the tables involved.

Please, give me some time.

Thanks,

Mattia