topic Replace Null With Value In Left Join in QlikView

Replace Null With Value In Left Join

Tue, 25 May 2010 21:09:13 GMT

This is probably a simple question:

If I am left joining two tables as in below

Load

item,

Resident Warehouse

Left Join (Warehouse) Load

item,

backlog

Resident backlogtbl

How do I make the backlog bring in zero to the warehouse table when the item does not exist in the backlogtbl? I would assume it should be someing like if(backlog = null(),0,backlog), but this doesn't seem to work.

Replace Null With Value In Left Join

fernandotoledo — Tue, 25 May 2010 21:27:22 GMT

You have to load the table again using this IF() statement, then drop the previous loaded table.

It work this way because you have to complete the load of a "temporary" table and then "Resident Load" from it to use the null() values generated by the join in an expression.

Replace Null With Value In Left Join

johnw — Tue, 25 May 2010 23:58:46 GMT

Is ITEM a unique key to both tables? If so, then using just a temporary field and another left join may (or may not) be more efficient than using a whole temporary table.

LEFT JOIN (WAREHOUSE)
LOAD ITEM, BACKLOG as TEMPBACKLOG
RESIDENT BACKLOGTBL
;
LEFT JOIN (WAREHOUSE)
LOAD ITEM, if(len(TEMPBACKLOG),TEMPBACKLOG,0) as BACKLOG
RESIDENT WAREHOUSE
;
DROP FIELD TEMPBACKLOG
;

Also potentially worth testing is a mapping load:

[Backlog]:
MAPPING LOAD ITEM, BACKLOG
RESIDENT BACKLOGTBL
;
LEFT JOIN (WAREHOUSE)
LOAD ITEM, applymap('Backlog',ITEM,0) as BACKLOG
RESIDENT WAREHOUSE
;

Even better is if you can do the mapping during the original load of the warehouse table instead of waiting until after it is loaded. Well, unless it messes up an optimized QVD load or something.

Replace Null With Value In Left Join

Wed, 26 May 2010 14:37:01 GMT

I performed the second resident load as you mentioned, but strangely the if(backlog = null(),0,backlog) function did not work. Instead, I reviewed the post by John Witherspoon who suggested if(len(backlog),backlog,0) and this worked. I find it interesting that the value stored when a left join does not find is not null(). Is this correct?

Replace Null With Value In Left Join

Wed, 26 May 2010 15:15:15 GMT

I had an issue once where FIELD=null() wasn't returning true but IsNull(FIELD) would. I have also had situations where I have to use '' (thats two single quotes) to make an empty set. Those might end up working for you as well.

Replace Null With Value In Left Join

johnw — Wed, 26 May 2010 17:01:10 GMT

I haven't checked recently, but isnull() didn't work very well in 64-bit versions of the product. So I always use len() to check for nulls, as it works in both 32 and 64 bit versions.

Replace Null With Value In Left Join

Wed, 26 May 2010 19:56:42 GMT

Thanks for the heads up. We use 32 bit desktop development environments and a 64 bit server so we may be having performance issues that aren't showing up in development.