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

[#1]

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.

[jpenuliar]

If you are using Straight  or Pivot Table objects, you can do Presentation Property>> Null Symbol or Missing Symbol textboxes, but not with other charts.

That is why you can do

If-Isnull combo in your 3 fields.

[#3]

Hi Gabriel ,

i placed the your code before my fact table .

null is not getting replaced .

i  placed supplier code in place of matched suppler in  MAP [matched supplier] USING Map_Supplier_Null;

please tell me where i am going wrong

[Gabriel]

This part

Map_Supplier_Null:

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

Has to be before your FACT table and

MAP ... USING has to be before your FACT table as well or table that has the Field Name you want to rename the NULL value. As this field might not be in your FACT table

[sasikanth]

hi,

go to the script where you have 3 fields

global suplier

domestic supplier

local supplier

write condition for every field

if(IsNull(global suplier),'Unmapped supplier',global suplier) as global suplier,

if(IsNull(domestic supplier),'Unmapped supplier',domestic supplier) as domestic supplier

if(IsNull(local supplier),'Unmapped supplier',local supplier) as local supplier

give a try

[#6]

hi

i have posupplier code in fact and dimesion table.i am linking the these 2 tables by using posupplier code.

if there is a row with  certain posupplier code is in fact table (ex:012).but there is no entry with 012 in dimesion table . in that case how i can say that supplier code as 'unmapped' in bar chart with drill down group ?

please suggest .

[#7]

i placed the code as you suggested but still i dd get ant thing.

i am getting '-' in dimesion value in chart

[#8 · Accepted solution]

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

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

[#9]

Hi joe,

thanks a lot .it worked

[#10]

No worries glad to help

[Ragnar]

Posting a reply to say thank you for your if(IsNull) expression. Been trying for days to display isnull data then realized that isnull has no value and it needed to be renamed to produce a value that can then display isnull data.

Thanks sasikanth.