topic Re: Missing records using Outer Join in QlikView

Missing records using Outer Join

Anonymous — Fri, 27 Oct 2017 08:18:50 GMT

Hi,

I have met some issues with Outer Join.

While working on small tables, it does work properly. However, when I tried joining 2 big tables with 4 data fields in common, there are some missing keys and records.

Given an example below, the record boxed in red (while joining 2 tables with many columns) went missing. Any idea what's wrong?

Kindly advise if you have met such problem before. Thanks in advance!

Re: Missing records using Outer Join

micheledenardi — Fri, 27 Oct 2017 08:24:35 GMT

There is nothing wrong on the Output table.. Outer join it's working properly....

Re: Missing records using Outer Join

micheledenardi — Fri, 27 Oct 2017 08:26:37 GMT

Which is your expected result ?

Re: Missing records using Outer Join

Anonymous — Fri, 27 Oct 2017 08:34:05 GMT

Hi Michele,

That's right. But currently, the last row was missing.

Based on my observation, it seems to be working like a left join E.g. Left Join (Table 1) instead of outer join even though I explicitly mentioned it.. Could it be because there are too many columns in Table 1 (about 20+ fields)?

Re: Missing records using Outer Join

micheledenardi — Fri, 27 Oct 2017 09:30:25 GMT

Can you share your code ?

Re: Missing records using Outer Join

Anonymous — Fri, 27 Oct 2017 10:07:10 GMT

gg 678 hh this combination may not be avialable in table1 or format or datatype may be changed for field1 field2 field3

Re: Missing records using Outer Join

Anonymous — Fri, 27 Oct 2017 10:31:12 GMT

Hi Kulwant,

Thank you for your response.

Are you referring to original Table 1 or output table, which is also called Table 1? Since I have used

Outer Join (Table 1)

GG-678-HH record is residing in Table 2 when table is loaded. (checked)

However, when I tried to do an outer join on Table 1 and Table 2, it did not appear in the combined table.

Only records highlighted in yellow, and records from Table 1 are found, which is how it is supposed to be.

But records unique to Table 2 are not showing up.

Re: Missing records using Outer Join

Anonymous — Fri, 27 Oct 2017 11:45:23 GMT

Using your data its not missing records

Table1:

load F1&F2&F3 as key ,F4,F5;

LOAD * INLINE [

F1, F2, F3, F4, F5

aa, 123, dd, 1234567, abcd

bb, 234, ee, 2234567, efgh

cc, 345, ee, 3345678, jklm

];

outer join(Table1)

Table2:

Load F1&F2&F3 as key,F6;

LOAD * INLINE [

F1, F2, F3, F6

aa, 123, dd, 111

gg, 678, hh, 222

];

output: