Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

Tags (4)
1 Solution

Accepted Solutions
Not applicable

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.

2 Replies
Not applicable

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.

Not applicable

Finding nulls by joining two tables

Thanks this will work