Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
It worked! I appreciate your help.
Thank you very much!