Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
karimeddini_sla
Partner - Contributor III
Partner - Contributor III

Left Join followed by Outer Join (and vice-versa)

I am trying to blend data from 2 sources, and which source takes precedence depends on the status. It seems like whichever join is 2nd does actually happen, and I do not understand why. Is this a bug or a subtle intended behavior?

This is a bare-bones example of my script:

foo:
LOAD *
INLINE [key,status_foo,val
1,b,qwerty
2,b,asdf
]
;

bar:
LOAD *
INLINE [key,status_bar
1,b
2,a
3,b
4,a
]
;

foobar_q:
OUTER JOIN(foo)
LOAD * RESIDENT bar WHERE status_bar = 'a';

foobar_b:
LEFT JOIN(foo)
LOAD * RESIDENT bar WHERE status_bar = 'b';

DROP TABLE bar;

This is the output I expect:

keystatus_foostatus_barval
1bbqwerty
2bqasdf
4-q-
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

I can't see anything wrong here.

Initially you are outer joining foo with bar for status_bar = a

here you will get output as    

key,        status_foo,     val,          status_bar

1,           b,                   qwerty,     -

2,           b,                   asdf,         a

4,          -                         -            a

Now you are Left joining foo (above resultant table) with bar

so the join will take care wrt key field (key&status_bar) and not only key field as foo table has now status_bar field also

so when you are doing left join you don't have 3 & b so you will not get data for 3 and b !

View solution in original post

5 Replies
MK_QSL
MVP
MVP

I can't see anything wrong here.

Initially you are outer joining foo with bar for status_bar = a

here you will get output as    

key,        status_foo,     val,          status_bar

1,           b,                   qwerty,     -

2,           b,                   asdf,         a

4,          -                         -            a

Now you are Left joining foo (above resultant table) with bar

so the join will take care wrt key field (key&status_bar) and not only key field as foo table has now status_bar field also

so when you are doing left join you don't have 3 & b so you will not get data for 3 and b !

karimeddini_sla
Partner - Contributor III
Partner - Contributor III
Author

I want "key=1,status_bar=b,status_foo=b" as well. Your mention that status_bar was part of the key for the 2nd join helped me realize how to get that. Thank you for your help.

MK_QSL
MVP
MVP

IF you got your answer, kindly mark appropriate answer.

Not applicable

Peter, your left join happending on both key & status_bar that leads to wrong results. Please find the below script.

foo:

LOAD *

INLINE [key,status_foo,val

1,b,qwerty

2,b,asdf

];

bar:

LOAD *

INLINE [key,status_bar

1,b

2,a

3,b

4,a

];

Join (foo)

LOAD key ,  status_bar Resident bar where status_bar = 'a' ;

Left Join (foo)

LOAD key ,  status_bar as temp_bar_b Resident bar where status_bar = 'b' ;

DROP Table bar;

foobar:

LOAD key , val , status_foo , if(IsNull(status_bar) , temp_bar_b , status_bar ) as status_bar Resident foo;

DROP Table foo;

Not applicable

Please find the attached qvw.