Replace Null With Value In Left Join

[#1]

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.

[fernandotoledo · Accepted solution]

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.

[johnw]

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.

[#4]

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?

[#5]

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.

[johnw]

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.

[#7]

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.