Tx swuehl, I've checked all info, but looks like it's not intended to work with join
in case like below, result of this join goes into 2 tables(?), I played with resident and nothing worked,
Bit confused. How to make resident load for single table which is result of Join:
LOAD * inline [ CatID, Amount
999, 999 ];
LOAD * inline [CatID, CatName
333, 'Charlie' ];
if(IsNUll(CatName), 'Other', CatName) as CatName, // ???
Amount resident Details_T;
Drop Table Details_T;
what Stefan said is correct. You have to perform NULL check once the data is left joinied. Until then you dont have a missing items. TRY BELOW
Sub as [CatID]
CategoryName as Category,
text(Sub) as [CatID]
if(IsNUll(Category), 'Other', Category) as Category
Drop table [Detals];
A left join in QV works the same as a left join in SQL - the unmatched rows get nulls in the joined fields.
Rather than using a join, use ApplyMap:
Sub as [CatID],
ApplyMap('MapCat', Sub, 'Other')
This will replace unmatched Sub values with 'Other'.
Consider using a Mapping table and ApplyMap rather than the join.
Applymap will allow you to specify 'N/A' or any other text for unmatched values, and is far faster than joining tables.
If you need to join more fields then you can use several mapping tables and applymap commands.