Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
3,
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 |
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
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
3,
4,45
5,23
6,5
];
Tab1:
LOAD
if(Len(A)=0,0,A) as A,
Id
Resident Source;
DROP Table Source;
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