5 Replies Latest reply: Dec 16, 2015 2:47 AM by Nir Gil RSS

    show null (missing) as zero value

      Hi,

      Is there a way that when there is no value in a specific date\s the Qlik will convert  the missing value to  a ZERO value (showing the date with value=zero instead of ignoring\not presenting that date at all.

      This way when I have a day\s with no value the line graph will go down to zero and up again at the next date for which a value exist?

       

      For example, if I have the following table:

       

      7.12.15    90

      10.12.15  100

      11.12.15  100

      12.12.15  90

      14.12.15  110

       

       

      This should be  converted to :

       

      7.12.15    90

      8.12.15    0

      9.12.15    0

      10.12.15  100

      11.12.15  100

      12.12.15  90

      13.12.15  0

      14.12.15  110

       

      Your help will be appreciated,

      Nir

        • Re: show null (missing) as zero value
          Marcus Sommer

          You could replace NULL with a value within the script with something like:

           

          if(len(trim(Field))=0, 0, Field)

           

          Possible is also to use statements like "NullAsValue" and "NullValue".

           

          If you need to use "Suppress NULL" within the chart-options a replacing with 0 isn't helpful and you need to manipulate it with something like 0.000000000000000001.

           

          - Marcus

          • Re: show null (missing) as zero value
            ramu yeluru

            Hi gil,

            If we want todo thatin script level

             

            if( IsNull(fieldname) or len(fieldname) =0, 'defaultvalue' , fieldname) as fieldname

            • Re: show null (missing) as zero value
              bobbyraj santhiogu

              Hi,

               

              You have to create those datas with the value 0.

               

              For your example:

              You have to create those missing dates => Check out how to create a master calendar in case you don't know to create the dates.

              Then you give 0 as value to each date and then join for the dates that doen't exist in your datas.

               

              Here is an example:

               

              Fact:

              LOAD * INLINE [

              Date, Value

              7.12.15, 90

              10.12.15,  100

              11.12.15, 100

              12.12.15, 90

              14.12.15,  110

              ];

               

              Temp: 

              Load 

              min(Date) as minDate, 

              max(Date) as maxDate 

              Resident Fact;

               

              Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

              Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

               

              DROP Table Temp; 

               

              TempCalendar: 

              LOAD  

              Date($(varMinDate) + IterNo() - 1) as TempDate 

              AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

               

              JOIN (Fact)

              LOAD

              TempDate as Date

              0 as Value

              Resident

              TempCalendar

              Where

              not exists(Date,TempDate);

               

                • Re: show null (missing) as zero value

                  Hello sbobbyraj,

                  I gave the example only as an  example.

                  I do not wish to create the dates manually since I have many dates-hole in the database which are varied for different cases.

                  Let say you collect money, from different countries and in each country there are different cities and in every city there are a few bank. You collect money from each bank but sometimes (some dates) a bank does not deposit money, does not give you money that date.

                  Now you want to show in a table the money deposit (aggregation expression) by country, by city or by a specific bank\s.

                  And when I pick a specific bank, when there is no deposit in some days, I want:

                  A.To present  the missing days in my X line (time line)

                  B. And in those missing days to show zero values.

                   

                  Nir