Then after selecting CustomerKey (which associates two ID columns in two separate tables...which are different ID lists), I want to build two straight tables:
a) Straight Table with these
b) Straight Table with these
** But for the second Straight Table, I only want to show the OfferIDs, if they exist in the Table1
In SQL, I could use: where OfferID in (select ProspectID from Table1)
How can I do an equivalent 'where in' clause?
PS. This is only a simplified example...as my real data model has many more links and the tables have more fields... some of which are 'many-to-many tables' (i.e. I don't adding and union/concatenate 'ID' column to the link table would work).
The QlikView associative engine travels CustomerKey link field, but you want to ignore that and only show OfferIDs that correspond to identical ProspectID values?
You can do this using set analysis that resets CustomerID to all those values that comply with ProspectID = OfferID. But as your PS stated that you real situation is much more complex, we may be building a solution that only works for your two Customer tables, and not for your actual data model. For example, in this case you could create a key composed of both xxxxID and CustomerKey. Or because in Sales Analysis, both IDs usually concern the same Customer, you may as wel ignore the CustomerKey link and create a link field from the two IDs.