Null Handling in Qlik Sense Pivot

pradnya_amol — Sat, 16 Nov 2024 19:10:59 GMT

Hello All,

I have a query wherein Excel is used to pull 1 Dimension and the Metrics are associated with it in QVDs. I'm ending with Null value on Qlik Sense UI: Pivot object. Like in Qlik View, we don't have option to rename the "Null Symbol" as Unknown or NOt assigned. What should I do? Data is hereby. Level 5 field is available in QVD, Excel providing me the Level 1 Field against which I need to frame the measures and I'm getting '-' here on UI which I need to replace it by 'Unknown'. I tried mending the data model by using Mapping Load, Left / Right Join; even on UI I did figure len(field) but no use.
Help is appreciated.

BFC Cost Category Level 1	BFC Cost Category Level 5
Permanent Personnel Costs	Permanent personnel costs excl. SEV
Temporary personnel costs	Temporary personnel costs
Travel and expenses	Travel and expenses
Consultancy	Consultancy
Other 3rd Party Services	Other 3rd party services
Depreciation & amortization	Depr. & amortization
Direct Other	Advert. Promot. Mkt Res. Com Mat.
Direct Other	Consumables
Direct Other	Other Direct Other
Direct Other	Charges in
Logistic Costs	Freight system accounts
Logistic Costs	Freight sea
Logistic Costs	Freight air
Logistic Costs	Freight road
Logistic Costs	Freight other
Logistic Costs	Duties
Logistic Costs	Warehousing
Site costs	Waste and incineration
Site costs	Other production manuf. Costs
Site costs	Other site costs
Site costs	Repairs and maintenance
Site costs	Energy & utilities
Direct Other	Charges out
Direct Other	Direct Other
Site costs	Site costs
Permanent Personnel Costs	Severance
Site costs	Information Systems

Qlik Code:

BFC_Desc:
Mapping LOAD
If(Len(Trim (name))> 0,name,'<Not Assigned>') as [Cost Center Element.BFC Cost Category (MD)],
If(Len(Trim (ldesc1))> 0,ldesc1,'<Not Assigned>') as [BFC Cost Category Desc]
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Extraction Layer\Global\BFC\MD\IFRS CostCategory\QVD\E_G_BFC_MD_IFRSCostCategory_F_V01.QVD]
(qvd) where not Match(name,'3RD');
[Cost_Center_Element]:
LOAD
Trim([Cost Center Element.Cost Center/Element (MD)]) as [Cost Center/Element],
[Cost Center Element.Subaccount 1 (MD)] as [SubAccount 1 Code],
if([Cost Center Element.Subaccount 1_Medium_Description (MD)]='Perm Personnel Costs','Permanent personnel costs excl. SEV',[Cost Center Element.Subaccount 1_Medium_Description (MD)]) as [SubAccount],
If(Len(Trim ([Cost Center Element.BFC Cost Category (MD)] ))> 0,[Cost Center Element.BFC Cost Category (MD)] ,'<Not Assigned>') as [BFC Cost Category],
ApplyMap('BFC_Desc',[Cost Center Element.BFC Cost Category (MD)],'<Not Assigned>') as [BFC Cost Category Desc]
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Transformation Layer\Global\MRH\MD\Cost_Center_Element\QVD\T_G_MRH_MD_Cost_Center_Element_FULL.qvd]
(qvd) where Match([Cost Center Element.MRH COA Level 8 (MD)],'80069000','80273050') and match([Cost Center Element.Calendar Year (MD)],(Year(Today())-2),Year(Today())) ;

right join([Cost_Center_Element])

LOAD ID,
If(Len(Trim ([BFC Cost Category Level 1]))> 0,[BFC Cost Category Level 1],'<Not Assigned>') as [BFC Cost Category Level 1],
trim([BFC Cost Category Level 5]) as [BFC Cost Category Desc]
FROM
[\\deawiwqlvt041.syngentaaws.org\QlikView\Backup\PradnyaP\Mahesh and Pranav\FE Mappiing.xlsx]
(ooxml, embedded labels, table is [New Cost Categories]);

Re: Null Handling in Qlik Sense Pivot

lorenzoconforti — Tue, 04 Feb 2020 12:14:34 GMT

Try with NullAsValue in your script

NullAsValue *;
SET NullValue='Unknown';

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/NullAsValue.htm

topic Re: Null Handling in Qlik Sense Pivot in App Development

Null Handling in Qlik Sense Pivot

Re: Null Handling in Qlik Sense Pivot