topic Re: Re-naming Null Value in QlikView

Re-naming Null Value

Wed, 08 Oct 2014 21:51:28 GMT

I am having problems re-naming null values through script. I have scoured these forums and many of the responses to similar questions are to view a .pdf document titled Null and Nothing. While I'm sure this is helpful for more experienced users, I have not found the information I'm looking for and I'm hoping for more specific help than a link to that .pdf file.

What I am trying to accomplish is to replace the null values specifically under the Alloc_Instr_Name fields (in my specific case), with the value, 'Other'.

Everything I've read says this is best accomplished through the script rather than a calculated dimension. I've tried two methods.

One is to use the following at the beginning of the script:

SET NullValue = 'Other';

Nullasvalue Alloc_Inst_Name;

The script runs fine but the null values do not change.

The other method I've seen is the following:

if(isnull(Alloc_Instr_Name),'Other',Alloc_Instr_Name) as Alloc_Instr_Name

I've tried this as well but no luck. Is there a specific place in the script that would need to go to work?

Please keep in mind I'm extremely new to any type of coding whatsoever, Qlikview or otherwise...

Thank you,

-J D.

Re: Re-naming Null Value

antoniotiman — Wed, 08 Oct 2014 22:00:44 GMT

Hi,

in Grafic Tab of Your Tab change '-' with 'Others'

Regards,

Antonio

Re: Re-naming Null Value

anbu1984 — Thu, 09 Oct 2014 01:15:10 GMT

1.Add SET NullInterpret = '';

SET NullInterpret = '';

SET NullValue = 'Other';

Nullasvalue Alloc_Inst_Name;

2. if(Len(Trim(Alloc_Instr_Name))=0,'Other',Alloc_Instr_Name) as Alloc_Instr_Name

Re: Re-naming Null Value

Thu, 09 Oct 2014 21:40:16 GMT

Neither of those solutions work. The SET NullInterpret runs fine but nothing changes when it's loaded. And the...

if(Len(Trim(Alloc_Instr_Name))=0,'Other',Alloc_Instr_Name) as Alloc_Instr_Name

...does not work (the comma after the =0 appears red).

Below is what my script looks like. Where would I need to place these lines of script in context of what I have for it to work?

Thanks

Re: Re-naming Null Value

phaneendra_kunc — Thu, 09 Oct 2014 21:52:13 GMT

Below should work if your filed has NULL

Load [Account Id] as [Acct ID],

....

....,

if(Len(Trim(Alloc_Instr_Name))=0,'Other', Alloc_Instr_Name) as Alloc_Instr_Name

From

[....................................................................qvd] (qvd);

Is it really a Null or do you have DASH "-" in the data. Can you share you QVD?

Re: Re-naming Null Value

Thu, 09 Oct 2014 22:01:34 GMT

Hi J D,

Solution 1 (no coding required):

Go to the chart properties > Presentation and in the bottom-left corner you should find 'Null Symbol'. Change the '-' to 'Other' and that should work (note that this will change *all* null values in the chart to 'Other', not just those present in the Alloc_Instr_Name column).

Solution 2 (a little coding required):

Echoing Anbu and Phaneendra above, the length and trim functions should work as in the following screenshot:

Yours,

William

Re: Re-naming Null Value

Thu, 09 Oct 2014 22:03:18 GMT

try in your script editor

IF(IsNull(Alloc_Instr_Name) or Len(Trim(Replace(Alloc_Instr_Name,'-',''))) = 0, 'Other', Alloc_Instr_Name) as Alloc_Instr_Name,

Re: Re-naming Null Value

Anonymous — Fri, 10 Oct 2014 00:57:51 GMT

Hi Above option you will get under presentation tab. and which will replace in sheet object(not specific to one column)

Regards,

Chinna

Re: Re-naming Null Value

Thu, 16 Oct 2014 16:59:23 GMT

Hi J D,

Did any of the above suggestions help with your problem? If they did, or if you solved it yourself, please close this thread and/or mark the relevant response as correct so that we can keep the community organised.

Yours,

William