Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
kumarreddy257
Contributor

handling null values

Hi,

As in screen shot I have data like this after I made some calculations. But what my requirement is in between 02/13/2015 and 02/21/2015 dates have been missed. I want to display '0' where dates have been missed.

like this i want to display.

02/13/2015  106,237  

02/14/2015    0

02/15/2015     0

02/16/2015     0

02/17/2015     0

02/18/2015

02/19/2015

.

.

02/21/2015    40.195

Pls help me on this.

Thanks

19 Replies
Highlighted

Re: handling null values

This is possible once you add a MasterCalendar which will have all the dates for you. Look here for the MasterCalendar Script: Creating A Master Calendar

HTH

Best,

Sunny

Highlighted
amit_saini
Honored Contributor III

Re: handling null values

Kumar,

Try:

If(ISNULL(Date), 0, Date) as Calculates dimension.

Thanks,
AS

Highlighted

Re: handling null values

HI

Do you have all dates?

If yes,  Uncheck Suppress Zero values in presentation tab.

If no, then create Master Calendar for all dates and follow the above steps.

Highlighted
MVP
MVP

Re: handling null values

Temp:

Load

  Date(Date#(Date,'MM/DD/YYYY')) as Date,

  Value

Inline

[

  Date, Value

  02/13/2015, 106273

  02/21/2015, 40195

];

MinMaxDate:

Load min(Date) as MinDate, Max(Date) as MaxDate Resident Temp;

Let vMinDate = Num(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = Num(Peek('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

Join (Temp)

Load

  Date($(vMinDate) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

NoConcatenate

Final:

Load

  Date,

  If(IsNull(Value) or Len(Trim(Value))=0, 0, Value) as Value

Resident Temp

Order By Date;

Drop Table Temp;

Highlighted
kumarreddy257
Contributor

Re: handling null values

I have all dates in time dimension.

Highlighted
MVP
MVP

Re: handling null values

Then untick suppress when value is null in dimension tab for your date field

Highlighted
kumarreddy257
Contributor

Re: handling null values

Hi,

I have uncheck in both dimension and presentation tab as well. But still it is displaying the same results.

In presentation tab

In Dimension Tab

Highlighted

Re: handling null values

Try unchecking 'Suppress Missing' on the presentation tab also.

Highlighted
Not applicable

Re: handling null values

try this If(len(Trim(Date))=0, 0, Date) as Calculated dimension. Deepak