Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
key | status_foo | status_bar | val |
---|---|---|---|
1 | b | b | qwerty |
2 | b | q | asdf |
4 | - | q | - |
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 !
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 !
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.
IF you got your answer, kindly mark appropriate answer.
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;
Please find the attached qvw.