converting Space values into # at IDT level

[former_member183904]

hi guys, i have a scenario where i am required to convert Blank values coming in on an attribute to convert it to # to show in webi variable screen value list

E.g

i have a field could region and its attribute is group , no bex behind this, relational IDT universe, when i go to the object to see the list of values my first value is "Blank" as in Space . the user community doesnt like the space value and want it changed to be # value. i could write some SQL (beginner level) inIDT on group level. need help with the statement.

has to be done in IDT, webi is not the option.

anybody got any ideas.

thanks

[former_member207052 · Accepted solution]

Refer the column directly and try the following

Case when len(trim(yourtable.column))=0 THEN '#' Else yourtable.column End

What is your underlying database ?

Regards,

Naras

[former_member183904 · Accepted solution]

none of the suggestion worked sorry guys but i figured it out.

so what i did was convert the underlying attribute to a dimension and now all the values are showing up and blank shows up, when i go to webi it just says not assigned which works.

moral: underlying attribute in IDT Blank values wont show up if its used as a variable in Webi prompt Screen. need to recreate the attribute as a dimension in IDT then it will work.

[former_member183904]

guys just to clarify, this is not in side the report, it is inside the Variable selection screen from master data.

[amitrathi239]

NVL or replace you can use in the IDT universe object.After that add prompt on that object.

[Former Member]

you can try

case when trim(measure) is null then 999999999 else measure.

Thanks,

Swapnil

[Former Member]

Hi,

if you want to show # in case when there is null value  for any measure having number datatype then you won't be able to do it.

As measure can take only numeric values so I would suggest in that case you can show some value that never going to exists like 999999999 .

So you can do it by

NVL(measure;999999999)

I hope that will make sense.

Thanks,

Swapnil

[amitrathi239]

Hi,

Use replace function in IDT object.

Replace(Object,'','#')

now blank values will be replaced with #.

or if value is null then use  ifnull function.

syntax is

nvl(Object,'#')

[former_member183904]

Tried NVL , doesnt work, neother does replace.