2 Replies Latest reply: May 23, 2011 5:55 PM by nechamakatan RSS

    Finding nulls by joining two tables

      I have two tables with customer ids and channel mapping.

      one is the complete list of customers (from a sql pull)

      The second has some of the customer ids with a mapping assigned.

      I need to map all the ones that were not assigned in the second table (by a set of rules) as "all other"

       

      The source for the mapping is a mixture of text files and sql so I created the table by doing concatenates.

      What I can't seem to get working some thing like the following

       

      set Mapping to "all other" when

      Table1_all customers, joined by customer ID to Table 2 where Table2 mapping is null

       

      I want to think this is a resident join opportunity ...

       

      Thanks for your help

        • Finding nulls by joining two tables

          First load the ID's that have already been mapped.  Then load the full list with a where clause like the one below in a new table:

          WHERE NOT exists(ID)

           

          Then drop the first table(the ones already mapped).  What you have left are all of the ID's that weren't previously mapped.  At this point I would just map all of these ("all other") and then do a concatenate load to get the ID"s that were already mapped into your table using WHERE NOT exists(ID) again.

           

          There are probably 10 other ways to do this but without knowing every little detail I think this one should be good enough.

          • Finding nulls by joining two tables

            Thanks this will work