Left join 2 loaded tables correct syntax

[43918084]

Sorry that I m new to Qlik sense and have trouble writing correct syntax for Left join. I have 3 tables, Test1 and Test 2 will combine together as "tableA" and map with Tmap.  I want the result to show all fields in "TableA", but only include ID in Tmap.  Hope I could have some expert guidance.  Many thanks

[Test1]

ID	Org	Mature	Fac	ID_1	Org_1	Mature_1	Fac_1
1	12/02/2013	11/30/2019	a		12/02/2013	08/30/2020
2	07/08/2016	02/15/2020	b	2	07/08/2016	09/27/2019	b
3	11/10/2015	11/30/2019	c	3	11/10/2015	08/30/2020	c
4	11/10/2015	11/30/2019	c	5	11/10/2015	08/30/2020	d
5	11/10/2015	11/30/2019	c	4	11/10/2015	08/30/2020	e

[Test2]

ID	Org	Mature	Fac	ID_1	Org_1	Mature_1
6	12/02/2013	11/30/2019	d		12/02/2013	08/30/2020
7	07/08/2016	02/15/2020	e	7	07/08/2016	09/27/2019
8	11/10/2015	11/30/2019	f	8	11/10/2015	08/30/2020
9	11/10/2015	11/30/2019	g	9	11/10/2015	08/30/2020

[Tmap]

ID

3

4

 Expected result is

ID	Org	Mature	Fac	ID_1	Org_1	Mature_1
3	11/10/2015	11/30/2019	c	3	11/10/2015	08/30/2020
4	11/10/2015	11/30/2019	c	5	11/10/2015	08/30/2020

[Taoufiq_Zarra · Accepted solution]

Hi,

May be this :

tableA:
LOAD * INLINE [
    ID, Org, Mature, Fac, ID_1, Org_1, Mature_1, Fac_1
    1, 12/02/2013, 11/30/2019, a,  , 12/02/2013, 08/30/2020,  
    2, 07/08/2016, 02/15/2020, b, 2, 07/08/2016, 09/27/2019, b
    3, 11/10/2015, 11/30/2019, c, 3, 11/10/2015, 08/30/2020, c
    4, 11/10/2015, 11/30/2019, c, 5, 11/10/2015, 08/30/2020, d
    5, 11/10/2015, 11/30/2019, c, 4, 11/10/2015, 08/30/2020, e
];


LOAD Null() as Fac_1,* INLINE [

    ID, Org, Mature, Fac, ID_1, Org_1, Mature_1
    6, 12/02/2013, 11/30/2019, d,  , 12/02/2013, 08/30/2020
    7, 07/08/2016, 02/15/2020, e, 7, 07/08/2016, 09/27/2019
    8, 11/10/2015, 11/30/2019, f, 8, 11/10/2015, 08/30/2020
    9, 11/10/2015, 11/30/2019, g, 9, 11/10/2015, 08/30/2020
];

right join


LOAD * INLINE [
    ID
    3
    4
];

output:

[Kushal_Chawda]

This approach will save few of the load time if data is huge. It will not load entire table instead load only ID defined in table 3. So You may not need to join table which you should avoid it if there is other way to do it. Also use force concatenate here otherwise your load will fail if any new column is added

Table3:
Load * Inline [
ID
3
4 ];

TableA:
LOAD
    ID,
    Org,
    Mature,
    Fac,
    ID_1,
    Org_1,
    Mature_1,
    Fac_1
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1)
where Exists(ID);

Concatenate(TableA)
LOAD
    ID,
    Org,
    Mature,
    Fac,
    ID_1,
    Org_1,
    Mature_1
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @2)
Where Exists(ID);

Drop table Table3;

[43918084]

Thank you so much for your expert advise.  It works perfect using the script.

In my case, I need to load all data in Table A for other analyses.  And the Table 3 is another analysis that I need to extract from Table A.

Does it mean I have to create 2 apps separately?

[Kushal_Chawda]

It depends on requirement. If your analysis is different and user don't want to look at the analysis in single app then you can create separate app. If user wants to look at both analysis in single app and there is a relationship between those analysis then you need to link tableA and table3 within single app

[43918084]

Thank you very much.  This is very helpful

[43918084]

Thank you very much again , Kush

Unfortunately, when I try to apply this method in other tables, it only shows the field in Table3. All other fields in Table A are empty.

May I seek your expertise again? Many thanks.