topic Re: Unorthodox (or conditional) Join in QlikView

Unorthodox (or conditional) Join

sunny_talwar — Tue, 28 Feb 2017 15:19:31 GMT

Hey Team -

I feel like I might have asked a similar question in the past, but was not able to find it. Here is a sample

Table1:

LOAD ID1,

If(Len(Trim(ID2)) > 0, ID2) as ID2,

Dim;

LOAD * INLINE [

ID1, ID2, Dim

10, , XYZ

20, 10, XYZ

20, 11, ABC

];

Table2:

LOAD * INLINE [

ID1, ID2, Value1

10, 1, 50

10, 2, 60

10, 3, 70

10, 4, 80

10, 5, 90

10, 6, 100

10, 7, 110

10, 8, 120

10, 9, 130

10, 10, 140

10, 11, 150

20, 10, 160

20, 11, 170

];

and this is the output I am hoping to get

ID1	ID2	Value1	Dim
10	1	50	XYZ
10	2	60	XYZ
10	3	70	XYZ
10	4	80	XYZ
10	5	90	XYZ
10	6	100	XYZ
10	7	110	XYZ
10	8	120	XYZ
10	9	130	XYZ
10	10	140	XYZ
10	11	150	XYZ
20	10	160	XYZ
20	11	170	ABC

Essentially the idea is that join on just ID1 when ID2 in table1 is null. Else, join on ID1 and ID2. What would be the best way to do this?

Re: Unorthodox (or conditional) Join

sunny_talwar — Tue, 28 Feb 2017 15:29:49 GMT

Here is one way, but is there anything better?

Table1:

LOAD ID1,

If(Len(Trim(ID2)) > 0, ID2) as ID2,

Dim;

LOAD * INLINE [

ID1, ID2, Dim

10, , XYZ

20, 10, XYZ

20, 11, ABC

];

Table2:

LOAD * INLINE [

ID1, ID2, Value1

10, 1, 50

10, 2, 60

10, 3, 70

10, 4, 80

10, 5, 90

10, 6, 100

10, 7, 110

10, 8, 120

10, 9, 130

10, 10, 140

10, 11, 150

20, 10, 160

20, 11, 170

];

Left Join (Table2)

LOAD ID1,

ID2,

Dim as Dim1

Resident Table1

Where not IsNull(ID2);

Left Join (Table2)

LOAD ID1,

Dim as Dim2

Resident Table1

Where IsNull(ID2);

DROP Table Table1;

FinalTable:

LOAD ID1,

ID2,

If(Len(Trim(Dim1)) = 0, Dim2, Dim1) as Dim,

Value1

Resident Table2;

DROP Table Table2;

Re: Unorthodox (or conditional) Join

MK_QSL — Tue, 28 Feb 2017 15:40:09 GMT

Table1:

Mapping LOAD

ID1 & ID2 as Key,

Dim

;

LOAD * INLINE [

ID1, ID2, Dim

10, , XYZ

20, 10, XYZ

20, 11, ABC

];

Table2:

Mapping LOAD

ID1 as Key2,

Dim

Where Len(TRIM(ID2))=0;

LOAD * INLINE [

ID1, ID2, Dim

10, , XYZ

20, 10, XYZ

20, 11, ABC

];

Table2:

LOAD

ID1,

ID2,

ID1 & ID2,

ApplyMap('Table1',ID1&ID2, ApplyMap('Table2',ID1,Null())) as Dim,

Value1

INLINE

[

ID1, ID2, Value1

10, 1, 50

10, 2, 60

10, 3, 70

10, 4, 80

10, 5, 90

10, 6, 100

10, 7, 110

10, 8, 120

10, 9, 130

10, 10, 140

10, 11, 150

20, 10, 160

20, 11, 170

];

Re: Unorthodox (or conditional) Join

sunny_talwar — Tue, 28 Feb 2017 15:47:57 GMT

This is the same one I came up with. I just need to check if I can replicate this in my original application or not.

Thanks,

Sunny