topic Re: show null (missing) as zero value in QlikView

show null (missing) as zero value

Tue, 15 Dec 2015 09:38:02 GMT

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 — Tue, 15 Dec 2015 12:33:48 GMT

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

Tue, 15 Dec 2015 12:43:02 GMT

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

Tue, 15 Dec 2015 14:15:10 GMT

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

Re: show null (missing) as zero value

Anonymous — Tue, 15 Dec 2015 14:51:45 GMT

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

Wed, 16 Dec 2015 07:47:53 GMT

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