Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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:
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:
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;
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?
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
Thank you very much. This is very helpful
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.