Replace nulls

[xarapre7]

Hi QV experts!

I have an excel file for Product Hierarchy and joined it to Material table and would like to have values of  '<N/A>' for nulls and 'Others' for those with values other than those that have been categorized.  I have the below script but it's only giving me the values for Product Categories.  It's not giving the '<N/A>' and 'Other' values.

Left join (Material)

LOAD Distinct

  ProductHierarchy5Desc as [Product Hierarchy L5 Desc],

  If(Isnull([Product Categories]) or len(trim([Product Categories])) = 0 or trim([Product Categories]) = '' or [Product Categories] = '-'  ,'Other',[Product Categories]) AS 'ProductCat'

FROM

  $(vExternalDataPath)\Product Grouping.xlsx

  (ooxml, embedded labels, table is [Dispensing Prod Hier]);

This is the result but, it's not showing the 'Others'.  I also need to show the '<N/A>' for nulls or missing. 

Can you please help with the correct expression?  Thank you in advance!

[aarkay29]

since you are doing left join, the nulls must have been in the material table

try this

Left join (Material)

LOAD Distinct

  ProductHierarchy5Desc as [Product Hierarchy L5 Desc],

[Product Categories]

FROM

  $(vExternalDataPath)\Product Grouping.xlsx

  (ooxml, embedded labels, table is [Dispensing Prod Hier]);

Material_New:

Load *,

  If(Isnull([Product Categories]) or len(trim([Product Categories])) = 0 or trim([Product Categories]) = '' or [Product Categories] = '-'  ,'Other',[Product Categories]) AS 'ProductCat'

Resident Material;

Dropt Table Material;

[xarapre7]

It worked!  I appreciate your help.

Thank you very much!