topic Re: handling null values in QlikView

handling null values

Anonymous — Thu, 13 Aug 2015 09:59:04 GMT

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

Re: handling null values

sunny_talwar — Thu, 13 Aug 2015 10:03:07 GMT

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

Re: handling null values

amit_saini — Thu, 13 Aug 2015 10:03:50 GMT

Kumar,

Try:

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

Thanks,
AS

Re: handling null values

MayilVahanan — Thu, 13 Aug 2015 10:06:15 GMT

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.

Re: handling null values

MK_QSL — Thu, 13 Aug 2015 10:08:47 GMT

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;

Re: handling null values

Anonymous — Thu, 13 Aug 2015 10:09:48 GMT

I have all dates in time dimension.

Re: handling null values

MK_QSL — Thu, 13 Aug 2015 10:13:11 GMT

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

Re: handling null values

Anonymous — Thu, 13 Aug 2015 10:20:47 GMT

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

Re: handling null values

sunny_talwar — Thu, 13 Aug 2015 10:22:08 GMT

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

Re: handling null values

Thu, 13 Aug 2015 10:25:28 GMT

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

Re: handling null values

Anonymous — Thu, 13 Aug 2015 10:28:08 GMT

I did it, but no use. Actually this date field is time stamp.

Re: handling null values

sasiparupudi1 — Thu, 13 Aug 2015 10:40:06 GMT

in the script,

load Date,

if (len(Value)=0,0,Value) as Value

load inline

[

Date Value

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

];

in your straighttable uncheck supress zero values option

hth

Sasi

Re: handling null values

qlikviewwizard — Thu, 13 Aug 2015 10:42:24 GMT

‌hi Kiran,

can you attach sample file to demonstrate your issue? Thank you

Re: handling null values

Anonymous — Thu, 13 Aug 2015 11:07:01 GMT

it is an incremental load. Everything will get from database. We should not use inline load here.

Re: handling null values

Anonymous — Thu, 13 Aug 2015 11:18:15 GMT

Deepak, I incorporated the same.

See the screen shot. there is no at the end it is adding '0' after made it as ascending order.

Re: handling null values

Anonymous — Thu, 13 Aug 2015 12:01:03 GMT

All files are available in remote desk top. I can't copy into my local machine

Re: handling null values

sasiparupudi1 — Thu, 13 Aug 2015 12:27:47 GMT

the inline load I have given as an example only. You could use the

condition if (len(Value)=0,0,Value) as Value

in your load script for null values

hth

Sasi

Re: handling null values

qlikviewwizard — Thu, 13 Aug 2015 14:26:23 GMT

Hi Kumar,

Pls check the attachment. Hope this will help you.

Re: handling null values

Anonymous — Thu, 13 Aug 2015 14:51:20 GMT

Hi,

In ur example still dimensions are missing, but in my date dimension the dates are there. To the corresponding date i don't have any value in expressions. Like this some of values are missing in expressions.

In above table the highlighted with yellow dates have been missed. because there is count and sum to the corresponding dates. Like 2 table it should be populate missing dates where null value is there along with it will be display 0 in place of null.

Regards,

kumar

Re: handling null values

qlikviewwizard — Thu, 13 Aug 2015 15:05:58 GMT

Hi kumarreddy257

Please provide the sample data with qvw to demonstrate your requirement. It will help the folks in easy understand.

Thank you.