find unmatched records

[#1]

Kindly help me figure out how to find unmatched records from 2 tables.  If value of field CHONO in table1 does not exist in table2 then create record in table3.  Thank you

[matt_crowther]

I'd look to do an Outer join between the 2 tables to create amaster table then perform a Resident load from the Master where the rows aren't complete and thefore must be Un-Matched records (both A not in B and vice versa - by controlling the Where clause you can return only A not in B or B not in A).

Hope that helps,

Matt - Visual Analytics Ltd

[#3]

way late I know but just did this today:

very easy in SQL:

Table3:

Load *;

SQL

select t1.CHONO

from Table1 t1

left join Table2 t2 on t1.CHONO=t2.CHONO

where t2.CHONO is null;

If you want distinct list add distinct after select.

[Anonymous]

klangley - Good set-based solution for SQL as it pushes the heavy lifting off to the database server. But this solution will only find unmatched in SQL, not in QlikView.  For example (as was my situation), I needed to find unmatched in two qvd's.I was able to leverage Matthew Crowther's solution.