1 Reply Latest reply: Feb 10, 2016 11:47 AM by Peter Cammaert RSS

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

    Jarrell Dunson

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

        • Re: Straight Table 'Where In' clause? [Not Load Script]
          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