topic Re: calculating time values and having null values. in QlikView

calculating time values and having null values.

andries_bos — Wed, 06 Feb 2013 09:30:11 GMT

I'm loading time values from an excel spreadsheet: however not all of the time values are in the xls sheet present at time of rebuild.

I would like to show in different tables/ charts the calculated (sum) values of two time values ; despite one that could be empty (null?)

=Sum(time1 + time2)

This will work when time1 and time2 both have a value, but would fail when one of them is empty.

=interval(time1 + time2) however would work but as this is a calculated value, this could not be shown in a straight table.

I'm looking for two solutions: one to be used in a chart

and one within the loading script.

using my script :

interval([time1] + time)

AS Totaaltime,

does not seems to work.

what is the best practice dealing with null values?

Anonymous — Wed, 06 Feb 2013 09:38:58 GMT

No sure about the Chart expression, but for the script can you test if it is null and if so add zero instead ?

interval([time1] + if(len(time)>0,time,0)) AS Totaaltime,

I often have problems testing for nulls, with IsNull() function not returning what I expect. This is why I test length instead.

Jonathan

er_mohit — Wed, 06 Feb 2013 09:58:08 GMT

hiiiii

LOAD *,

Interval(if(Len(Time(time1,'hh:mm:ss')),time1,0)+if(Len(time(time2,'hh:mm:ss')),time2,0))as total;

LOAD * INLINE [

time1, time2

8:00:12

6:30:12

, 4:09:8

, 5:12:09

12:00:00, 7:15:48

];

hope it helps