Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

Left join 2 loaded tables correct syntax

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]

IDOrgMatureFacID_1Org_1Mature_1Fac_1
112/02/201311/30/2019a 12/02/201308/30/2020 
207/08/201602/15/2020b207/08/201609/27/2019b
311/10/201511/30/2019c311/10/201508/30/2020c
411/10/201511/30/2019c511/10/201508/30/2020d
511/10/201511/30/2019c411/10/201508/30/2020e

 

[Test2]

IDOrgMatureFacID_1Org_1Mature_1
612/02/201311/30/2019d 12/02/201308/30/2020
707/08/201602/15/2020e707/08/201609/27/2019
811/10/201511/30/2019f811/10/201508/30/2020
911/10/201511/30/2019g911/10/201508/30/2020

 

[Tmap]

ID
3
4

 Expected result is

IDOrgMatureFacID_1Org_1Mature_1
311/10/201511/30/2019c311/10/201508/30/2020
411/10/201511/30/2019c511/10/201508/30/2020

 

 

1 Solution

Accepted Solutions
Taoufiq_Zarra

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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

6 Replies
Taoufiq_Zarra

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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Thank you very much.  This is very helpful

43918084
Creator II
Creator II
Author

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.