show null (missing) as zero value

[#1]

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

[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

[#3]

Hi gil,

If we want todo thatin script level

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

[#4]

Hi Ramanjaneyulu Yeluru,

thank you for your effort.

I tried the if( IsNull(field name) =0, 'default value' , field name) as field name in the load script but it did not work for me.

Thank you for your effort.

Nir

[Anonymous]

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

[#6]

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