topic Re: Left join 2 loaded tables correct syntax in QlikView

Left join 2 loaded tables correct syntax

43918084 — Fri, 03 Jan 2020 18:26:08 GMT

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]

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

Re: Left join 2 loaded tables correct syntax

Taoufiq_Zarra — Fri, 03 Jan 2020 18:43:36 GMT

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:

Re: Left join 2 loaded tables correct syntax

Kushal_Chawda — Sun, 05 Jan 2020 11:04:02 GMT

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;

Re: Left join 2 loaded tables correct syntax

43918084 — Sat, 04 Jan 2020 04:02:25 GMT

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?

Re: Left join 2 loaded tables correct syntax

Kushal_Chawda — Sat, 04 Jan 2020 12:02:20 GMT

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

Re: Left join 2 loaded tables correct syntax

43918084 — Sat, 04 Jan 2020 15:00:30 GMT

Thank you very much. This is very helpful

Re: Left join 2 loaded tables correct syntax

43918084 — Sat, 04 Jan 2020 22:40:16 GMT

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.