topic Re: I need help to Left Join() 1 table with 2 another different tables in QlikView

I need help to Left Join() 1 table with 2 another different tables

Mattia — Sat, 16 Nov 2024 19:41:24 GMT

Hi,

i need to Left Join() a table called "TAR" with two different tables called "T" and "AMB".

When i've only one table, i write Left Join (name of table).

Is it possibile? What's the right expression to do this?

Thanks,

Mattia

Re: I need help to Left Join() 1 table with 2 another different tables

RuhanLigabue — Tue, 12 Nov 2019 16:19:55 GMT

Hi Mattia,

In this case I think this will help you, you can switch the * with the field names you want to select and Qlik will join the tables using the same name fields

TAR: SELECT * FROM TAR LEFT JOIN (TAR) SELECT * FROM T; LEFT JOIN (TAR) SELECT * FROM AMB;

Re: I need help to Left Join() 1 table with 2 another different tables

Mattia — Tue, 12 Nov 2019 16:34:20 GMT

Hi Ruhan,

thanks for the suggest.

So, with this expression, i can add 1 or more fields of table TAR to different tables T and AMB?

Re: I need help to Left Join() 1 table with 2 another different tables

RuhanLigabue — Tue, 12 Nov 2019 16:58:58 GMT

I thought you'd like to do the opposite, to get a field from tar on the other tables you should do it:

TAR: SELECT * //Select the fields you want to join FROM TAR; T: SELECT * FROM T; LEFT JOIN (T) SELECT * //Name the fields you want to join on T RESIDENT TAR; AMB: SELECT * FROM AMB; LEFT JOIN (AMB) SELECT * //Name the fields you want to join on AMB RESIDENT TAR;

You can do the same using LOAD instead of RESIDENT, I guess there will be no difference in performance (if both are in QVD).

Here is how to do using LOAD instead of RESIDENT:

T: SELECT * FROM T; LEFT JOIN (T) SELECT * //Name the fields you want to join on T FROM TAR; AMB: SELECT * FROM AMB; LEFT JOIN (AMB) SELECT * //Name the fields you want to join on AMB FROM TAR;

Re: I need help to Left Join() 1 table with 2 another different tables

Mattia — Tue, 12 Nov 2019 17:14:14 GMT

Thanks you so much for the detailed reply.

Tomorrow i'll try and i let you know!!!

Last thing: is it possibile to join the entire table or more fields instead of a single field?

Re: I need help to Left Join() 1 table with 2 another different tables

RuhanLigabue — Tue, 12 Nov 2019 17:20:20 GMT

You will join all the tables on SELECT, if you put a * you will load all fields, or you can do like this:

T: SELECT * FROM T; LEFT JOIN (T) SELECT ID, FieldName1, FieldName2 RESIDENT TAR;

Then you'll add only ID, FieldName1 and FieldName2 on T

Just make sure to select the field that connects T to TAR otherwise you'll do a Cartesian product

Re: I need help to Left Join() 1 table with 2 another different tables

Mattia — Tue, 12 Nov 2019 17:22:57 GMT

Thank you Ruhan!!!

Re: I need help to Left Join() 1 table with 2 another different tables

RuhanLigabue — Tue, 12 Nov 2019 17:23:10 GMT

You will join all the tables on SELECT, if you put a * you will load all fields, or you can do like this:

T: SELECT * FROM T; LEFT JOIN (T) SELECT ID, FieldName1, FieldName2 FROM TAR;

Then you'll add only ID, FieldName1 and FieldName2 on T

Just make sure to select the field that connects T to TAR otherwise you'll do a Cartesian product

Re: I need help to Left Join() 1 table with 2 another different tables

Mattia — Fri, 15 Nov 2019 13:45:04 GMT

Hi Ruhan e sorry for the delay.

i've tried your suggest but i'm in trouble. I think i didn't completely understand your solution.

So, here is attached a sample data file.

The output i want is the following:

all the fields of table "TAR" must be add to both tables "AMB2" and "DET"
all the fields of table "RES" must be add to both tables "AMB1" and "TES"

Please, could you bring me to the right direction?

Thanks,

Mattia

Re: I need help to Left Join() 1 table with 2 another different tables

RuhanLigabue — Mon, 18 Nov 2019 14:46:12 GMT

Hey Mattia,

I guess this code can work for you

/* all the fields of table "TAR" must be add to both tables "AMB2" and "DET" */ TAR: LOAD * INLINE [ CODE, VAL 897, 5 576, 10 253, 15 543, 20 923, 25 ]; AMB2: LOAD * INLINE [ COUNT, PROG, CODE, NUM 00000001, 1, 897, 1 00000001, 1, 576, 2 00000001, 2, 253, 3 00000002, 1, 897, 1 00000002, 1, 543, 2 00000003, 1, 923, 1 ]; LEFT JOIN (AMB2) LOAD * RESIDENT TAR; DET: 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 (DET) LOAD * RESIDENT TAR;

This code will load TAR and join it in the 2 tables (AMB2 and DET) using the code the only problem I can see is a lot of synthetic keys (fields with the same name in multiple tables) you can use alias to fix it.

And for the next 2 joins you can follow the logic in this script above.

Re: I need help to Left Join() 1 table with 2 another different tables

Mattia — Mon, 18 Nov 2019 14:59:16 GMT

Thank you Ruhan. Your solution works!

Please, if you can, i need help also for these open threads: https://community.qlik.com/t5/QlikView-Scripting/How-can-i-Left-Join-multiple-tables/td-p/1647823 and https://community.qlik.com/t5/QlikView-Scripting/How-can-i-insert-an-expression-in-other-load/td-p/1647824.

Mattia