topic Re: Replace nulls in QlikView

Replace nulls

xarapre7 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Replace nulls

aarkay29 — Tue, 10 Oct 2017 04:05:42 GMT

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;

Re: Replace nulls

xarapre7 — Tue, 10 Oct 2017 04:28:52 GMT

It worked! I appreciate your help.

Thank you very much!