10 Replies Latest reply: Sep 27, 2016 4:45 PM by Aehman K RSS

    Show null values as

    Aehman K

      Hello,

       

      I would like to show blank or null values as 'InProcess'

      I'm working on Qlik Sense, not Qlik View.

       

      Ex: If there are 2 values in a filed, A and B the third value is Blank. So instead of blank I want to show InProcess on Bar.

       

      I've tried multiple If condition and also IsNull but couldn't make it work.

       

      stalwar1

       

      Thanks.

        • Re: Show null values as
          Sunny Talwar

          Can you maybe make this change in the script itself?

            • Re: Show null values as
              Aehman K

              If I make change in script then will it effect all the other null values in other fields?

              I just want these Null values to change, not other Nulls in other fields.

                • Re: Show null values as
                  Sunny Talwar

                  All I am saying is that whatever dimension you are working with (assuming DimensionName1), may be create a copy of this so that you can still use DimensionName1 and then use the new one (let's call is DimensionName1_New) for this chart. And all you have to do is this in the script

                   

                  LOAD DimensionName1,

                            If(Len(Trim(DimensionName1)) = 0, 'InProcess', DimensionName1)  as DimensionName1_New

                  FROM ....

                    • Re: Show null values as
                      Aehman K

                      Create a copy of that dimension?

                        • Re: Show null values as
                          Sunny Talwar

                          I just gave you an example my friend

                           

                          LOAD DimensionName1,

                                    If(Len(Trim(DimensionName1)) = 0, 'InProcess', DimensionName1)  as DimensionName1_New

                          FROM ....

                            • Re: Show null values as
                              Aehman K

                              I loaded that in script but the count is not matching with what I've in db.

                              I've 10 counts of 'InProcess' and I'm getting 0 as count in dashboard.

                              Cus I'm taking =0 in script?

                              • Re: Show null values as
                                Aehman K

                                NullAsValue *;

                                SET ThousandSep=',';

                                SET DecimalSep='.';

                                SET MoneyThousandSep=',';

                                SET MoneyDecimalSep='.';

                                SET MoneyFormat='$#,##0.00;($#,##0.00)';

                                SET TimeFormat='h:mm:ss TT';

                                SET DateFormat='M/D/YYYY';

                                SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

                                SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                                SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                                SET NullValue='Inprocess';

                                SET NullDisplay='Null';

                                 

                                 

                                 

                                Can I use above script?

                                I do not want all null values to change but only one field values as'Inprocess'

                            • Re: Show null values as
                              Aehman K

                              I got it...

                               

                              with both scrips I tested and it's giving same result. I was using wrong measure so I wasn;t getting the null value as Inprocess.

                               

                              LOAD DimensionName1,

                                        If(Len(Trim(DimensionName1)) = 0, 'InProcess', DimensionName1)  as DimensionName1_New

                              FROM ....

                               

                               

                               

                              Or we can use this as well

                               

                               

                              LOAD Dim,

                                        if(isnull(Dim)< 0,'InProcess',Dim)  as Dim1

                                       

                              FROM....

                               

                               

                               

                              Thanks stalwar1

                        • Re: Show null values as
                          Alluraiah Allu

                          Hi

                           

                          try with alt ..functions in the Pivot or bar chart  ?? (replace  'InProcess' in the following)

                           

                          the following will give Alternative value   '1' in case if any value not found...

                           

                          Eg:

                           

                          Load the following script:

                          Alt_Example:

                          Load * Inline [

                          Month, LocalSales, ExRate

                          2013-01-01, 1000, 1.1

                          2013-02-01, 976,

                          2013-03-01, 1100, 1.2

                          ];


                          Expression in bar chart

                          Sum(LocalSales * Alt(ExRate,1))