Hi there,
I’ve got some trouble filtering data in the designer.
What I am trying to do is to use data a query to cleanse data of another query in order to get only the data I am looking for.
Background:
I am using Base_Match to generate matching groups for usage in SAP Information Steward. To have a higher matching I’m doing some operations ending in two different tables.
I’d like to clean the data of my desired table with the data of the other one. In Oracle I’d use MINUS (MSSQL would be EXCEPT?!) to get the data I need. But how do I do this in context of the designer?
I already found this: How can we perform a MINUS operation on tables in BO DI
But I simply don’t know exactly how to implement the lookup call and the following query in detail.
Or is there an even more elegant way to solve this?
It’s a shame that the Merge transform only does an union all and has no options to perform a transact or minus 
Any ideas?
BOBderBaumeister 
(BOB member since 2017-05-11)
I am not sure exactly what your question is on the lookup. It would go something like this:
SRC -> QRY_LKP -> QRY_FILT -> TRG
In the QRY_LKP you add lookup_ext column. It uses your keys from the source table to check for rows with the same key in the set you wish to minus. You can return any value that will always be set, I typically use the first column of my key. Default value for the lookup is set to Null.
In QRY_FILT you set the where clause to be the return column from the lookup_ext = NULL. This will allow all records that were not in the minus table to pass.
Done.
eepjr24 
(BOB member since 2005-09-16)
Thanks eepjr24, I’ll try that out.
Am I getting it right, that using template tables as sources for lookups and table comparisons is impossible?
BOBderBaumeister (BOB member since 2017-05-11)
That is correct. Template tables should never leave the Development Environment in any case. They are meant to speed development by providing a flexible working table that can change as you make discoveries and tweaks.
eepjr24 (BOB member since 2005-09-16)
FYI:
Your suggestion works perfectly well. Thanks again!
BOBderBaumeister (BOB member since 2017-05-11)