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

[Mattia]

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 

[RuhanLigabue]

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;

[Mattia]

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?

[RuhanLigabue]

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;

[Mattia]

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?

[RuhanLigabue]

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

[Mattia]

Thank you Ruhan!!!

[RuhanLigabue]

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

[Mattia]

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

[RuhanLigabue · Accepted solution]

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.