8 Replies Latest reply: Oct 16, 2014 12:59 PM by William Dib RSS

    Re-naming Null Value

    J Shaffer

      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.

        • Re: Re-naming Null Value
          Antonio Mancini

          Null.pngHi,

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

           

          Regards,

          Antonio

          • Re: Re-naming Null Value
            anbu cheliyan

            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


              • Re: Re-naming Null Value
                J Shaffer

                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

                  • Re: Re-naming Null Value
                    Phaneendra Kunche

                    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
                      William Dib

                      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

                  • 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,

                    • Re: Re-naming Null Value
                      chinna katiki

                      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

                      • Re: Re-naming Null Value
                        William Dib

                        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