Solved: how to rename null value to some default value in ... - Qlik Community

Report Inappropriate Content · ‎2015-05-08

Hi all,

i have drill down group on supplier.supplier at global,domestic,local .

my fact table and dimesion table are linked with supplier code. i am getting '-' in bar chart dimesion for no matched supplier . i want remname it to

'unmapped supp' .how i can get it.

thanks in advance .

Charan.

Report Inappropriate Content · ‎2015-05-08

Hi Charan,

so you have proper codes in your fact table but no corresponding value in your dimension table causing you a missing value null. So there is no row to remap at present.

What you can do is

FactTable:

Load

supplier_code

your field

......

Supplier_Dimension_temp:

Load Distinct

supplier_code

Resident FactTable;

left Join (Supplier_Dimension_temp)

Load

supplier_code

local supplier

domestic supplier

global supplier

from ......(your dimension source)

~~ at this point you have real nulls in your dimension table rather then missing values, so you can remap accordingly.

either using Gabriels map using or an isnull check

Map_Supplier_Null:

MAPPING LOAD
NULL(),
'unmapped supp'
Autogenerate 1;

Map local supplier, domestic supplier, global supplier using Map_Supplier_Null

Supplier_Dimension:

noconcatenate

Load

supplier_code

local supplier

domestic supplier

global supplier

Resident Supplier_Dimension_temp;

Drop table Supplier_Dimension_temp;

something along those lines should do the trick for

hope that helps

Joe

View solution in original post

swuehl · ‎2015-05-08

Have you checked the appropriate input boxes on presentation tab of the chart properties dialog?

sunilkumarqv · ‎2015-05-08

HI charan

use isnull function

If(isnull(Field)='Name',else codition)

jpenuliar · ‎2015-05-08

I would do that in script:

If(IsNull(Supplier),'UnNamed Supplier',Supplier) as Supplier,

Report Inappropriate Content · ‎2015-05-08

Hi swuehl,

i want to rename the '-' to 'unmapped supplier ' as value of field in drilldown group.

thanks..

Report Inappropriate Content · ‎2015-05-08

hi all,

it is not single field .it is a drill down group used as dimmesion.

thanks..

jpenuliar · ‎2015-05-08

and what are the fields in your drill down group?

Report Inappropriate Content · ‎2015-05-08

supplier are at 3 levels.

global suplier

domestic supplier

local supplier

above field are in supplier drill down group

sunilkumarqv · ‎2015-05-08

try this

If(getcurrentfield(yourgroupdimension)='' ,'un named ',Supplier)

share sample data

Gabriel · ‎2015-05-08

Hi,

I will create MAPPING table to handle NULL values as below

Map_Supplier_Null:

MAPPING LOAD
NULL(),
'unmapped supp'
Autogenerate 1;

And before your FACT table have another script like this

MAP [matched supplier] USING Map_Supplier_Null;

This will ensure all NULL values are renamed to unmapped supp.

how to rename null value to some default value in drilldown group