topic Re: How can i Left Join() multiple tables? in QlikView

How can i Left Join() multiple tables?

Mattia — Sat, 16 Nov 2024 01:55:33 GMT

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

Re: How can i Left Join() multiple tables?

simrankaur — Tue, 19 Nov 2019 07:57:54 GMT

TableA:

load * from tableA;

left join

load * from TableB;

left join

load * from TableC;

Re: How can i Left Join() multiple tables?

Yousef_Amarneh — Tue, 19 Nov 2019 08:28:09 GMT

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;

Re: How can i Left Join() multiple tables?

Mattia — Tue, 19 Nov 2019 11:11:03 GMT

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

Re: How can i Left Join() multiple tables?

manishnarang — Tue, 19 Nov 2019 11:26:23 GMT

Hi Mattia,

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

Re: How can i Left Join() multiple tables?

Yousef_Amarneh — Tue, 19 Nov 2019 11:29:47 GMT

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

Re: How can i Left Join() multiple tables?

Mattia — Tue, 19 Nov 2019 11:39:38 GMT

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
];

Re: How can i Left Join() multiple tables?

PrashantSangle — Tue, 19 Nov 2019 11:39:49 GMT

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

Re: How can i Left Join() multiple tables?

Mattia — Tue, 19 Nov 2019 11:40:54 GMT

Hi Yousef,

i can't see the attachment.

Error file system (-2147219196)

Re: How can i Left Join() multiple tables?

Mattia — Tue, 19 Nov 2019 11:56:25 GMT

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

Re: How can i Left Join() multiple tables?

Mattia — Tue, 19 Nov 2019 13:30:29 GMT

Hi,

here is attached the sample data file.

In the script, there are all the tables involved.

I need to join the tables in the following way:

from COM to TES

from TAR to DET and from DET to TES

from TAR to AMB2

from COM to AMB1 and from AMB1 to AMB2

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

I've tried also with applymap() but don't work. Other ideas are welcome!!!

Thanks,

Mattia