Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Re-naming Null Value

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'.

Image.JPG.jpg

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.

1 Solution

Accepted Solutions
Not applicable

Re: Re-naming Null Value

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).

11.PNG.png

Solution 2 (a little coding required):

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

2.PNG.png

Yours,

William

8 Replies
antoniotiman
Not applicable

Re: Re-naming Null Value

Null.pngHi,

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

Regards,

Antonio

anbu1984
Not applicable

Re: Re-naming Null Value

1.Add SET NullInterpret = '';


SET NullInterpret = '';

SET NullValue = 'Other';

Nullasvalue Alloc_Inst_Name;

Or

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


Not applicable

Re: Re-naming Null Value

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

Capture.JPG.jpg

phaneendra_kunc
Not applicable

Re: Re-naming Null Value

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?

Not applicable

Re: Re-naming Null Value

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).

11.PNG.png

Solution 2 (a little coding required):

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

2.PNG.png

Yours,

William

Not applicable

Re: Re-naming Null Value

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,

chinnakatikisg
Not applicable

Re: Re-naming Null Value

Null.png

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

Regards,

Chinna

Not applicable

Re: Re-naming Null Value

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