topic Finding nulls by joining two tables in QlikView

Finding nulls by joining two tables

Sun, 22 May 2011 20:21:11 GMT

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

Mon, 23 May 2011 20:41:31 GMT

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

Mon, 23 May 2011 21:55:23 GMT

Thanks this will work