Straight Table 'Where In' clause? [Not Load Script]

[jrdunson]

Hello everyone,

If I have three tables:

Table1:

ProspectID

CustomerKey

Table2:

OfferID

CustomerKey

LinkTable

Customer

CustomerKey

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

ProspectID

CustomerKey

b) Straight Table with these

OfferID

CustomerKey

** 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?

Jarrell

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).

[Peter_Cammaert]

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.

Peter