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]);
