topic Re: How to handle Missing Data in chart in QlikView

How to handle Missing Data in chart

Wed, 16 Apr 2014 13:11:58 GMT

Hi,

I wrote the below qlikview where in I have both null data points and missing data points. I can handle null points using expressions but I am unable to handle missing data points in my dashboard.

Any help is appreciated.

Thanks in advance.

Re: How to handle Missing Data in chart

its_anandrjs — Wed, 16 Apr 2014 13:31:10 GMT

Use Len() function for identify missing values but can you provide more data.

Like see the Eg:-

LOAD *,Len(A) as len;

LOAD * Inline

[

ID,A

1,14

2,12

4,45

5,23

6,5

];

If there is no value it gives 0 other wise length of the String

Here is

Id	A	len
6	5	1
2	12	2
1	14	2
5	23	2
4	45	2
3		0

Re: How to handle Missing Data in chart

MK_QSL — Wed, 16 Apr 2014 13:34:48 GMT

Change Value in your script with below ...

If(Value = 'null',Null(),Value) as Value

Now in your Line Chart

Change Expression

Alt(SUM(Value),0)

Presentation Tab

Untick Suppress Zero-Values

Re: How to handle Missing Data in chart

its_anandrjs — Wed, 16 Apr 2014 13:44:52 GMT

In any table load if you want to make any missing value 0 then load table like

Source:

LOAD * Inline

[

Id,A

1,14

2,12

4,45

5,23

6,5

];

Tab1:

LOAD

if(Len(A)=0,0,A) as A,

Resident Source;

DROP Table Source;

Re: How to handle Missing Data in chart

bertdijks — Wed, 16 Apr 2014 14:21:09 GMT

You probably want to see the hours for which there is no data?

See attache file for the solution.

What I did:

Convert your data to a real date field

Create calender table which is filled with all hours between the lowest and highest date in your data table

Load
Date(Date, 'D/M/YYYY h:mm:ss[.fff] TT') as Date,
Value;
LOAD Date,
Value
FROM
data.xls
(biff, embedded labels, table is Sheet1$);

Temp:
Load

               min(Date) as minDate,
               max(Date) as maxDate
Resident data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:

LOAD
               Date($(varMinDate)+ RowNo()/24 ) as Date
               AutoGenerate ($(varMaxDate)-$(varMinDate))*24;

Hopes this is what your where looking for!

Bert