topic Left outer join for multiple tables in QlikView

Left outer join for multiple tables

Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

Basically am looking for left outer join for multiple tables.

It will be an immense benefit for me if someone resolve the qry posted here. For details you may refer the attached documents. I reached level 1 not able to proceed further. For all of your information the JPG FIILE enclosed is the requirement and other documents are soft copy for the same.

In sql server it is bit easy to get the result

select

mst_column1,

mst_column2,

rep1_column1,

rep2_column1,

rep2_column3,

rep3_column5

from

master_table,

rep1_table,

rep2_table,

rep3_table

where

mst_dt = rep1_dt(+) and

mst_dt = rep2_dt(+) and

mst_dt = rep3_dt(+)

Please suggest how to get the same result through Qlik as my I have garbage data with me and data bases are different and servers are also different. So I created this own example....

Thanks all, have a great time.

With best regard

Sadhan Damal.

Re: Left outer join for multiple tables

swuehl — Tue, 08 Nov 2016 12:29:14 GMT

What about

mst:

LOAD mst_Dt,

mst_Desc,

mst_number,

mst_product

FROM

mst_Data.xlsx

(ooxml, embedded labels, table is Sheet1);

left join(mst)

LOAD Rep1_Dt as mst_Dt,

Rep1_Desc

FROM

rep1.xlsx

(ooxml, embedded labels, table is Sheet1);

left join(mst)

LOAD Rep2_Dt as mst_Dt,

Rep2_Desc

FROM

rep2.xlsx

(ooxml, embedded labels, table is Sheet1);

left join(mst)

LOAD rep3_Dt as mst_Dt,

rep3_Desc

FROM

rep3.xlsx

(ooxml, embedded labels, table is Sheet1);

mst_Desc	mst_Dt	mst_number	mst_product	Rep1_Desc	Rep2_Desc	rep3_Desc
mst_1	11/1/2016	1	mst__laksjf
mst_2	11/2/2016	2	mst__ggg
mst_3	11/3/2016	3	mst__edf
mst_4	11/4/2016	4	mst__sadfhgh
mst_5	11/5/2016	5	mst__ergbd
mst_6	11/6/2016	6	mst__zdxvrt			Rep3_6
mst_7	11/7/2016	7	mst__sdf			Rep3_7
mst_8	11/8/2016	8	mst__xczv	rep1_8	rep2_8
mst_9	11/9/2016	9	mst__xczgrfrhr	rep1_9	rep2_9
mst_10	11/10/2016	10	mst__sdfer	rep1_10
mst_11	11/11/2016	11	mst__asdgh	rep1_11
mst_12	11/12/2016	12	mst__sadgw	rep1_12	rep2_12	Rep3_12
mst_13	11/13/2016	13	mst__sadghy	rep1_13	rep2_13
mst_14	11/14/2016	14	mst__sadfae		rep2_14	Rep3_14
mst_15	11/15/2016	15	mst__asdfyre

Re: Left outer join for multiple tables

Wed, 09 Nov 2016 04:50:31 GMT

Many thanks for your precious time and kindness...

Actually I did like below by R&D....but your give me a conformation for the same.

mst:

LOAD mst_Dt,

mst_Desc,

mst_number,

mst_product

FROM

mst_Data.xlsx

(ooxml, embedded labels, table is Sheet1);

mstv1:

left join(mst)

LOAD Rep1_Dt as mst_Dt,

Rep1_Desc

FROM

rep1.xlsx

(ooxml, embedded labels, table is Sheet1);

rep2:

LOAD Rep2_Dt as mst_Dt,

Rep2_Desc

FROM

rep2.xlsx

(ooxml, embedded labels, table is Sheet1);

rep3:

LOAD rep3_Dt as mst_Dt,

rep3_Desc

FROM

rep3.xlsx

(ooxml, embedded labels, table is Sheet1);

Thanks a ton once again for your help.

Have a nice time ahead !!!

Re: Left outer join for multiple tables

swuehl — Wed, 09 Nov 2016 07:24:40 GMT

It seems this code is not joining the rep2 and rep3 tables to the master table, instead keeping them linked by mst_Dt.

Might also work and be a valid solution dependend on what you want to achieve further down the lines.

Re: Left outer join for multiple tables

Wed, 09 Nov 2016 07:38:31 GMT

You are right SIR, the qry posted by you was the right one and it will never fail the left outer join statement.

Thanks once again for your concern.