calculating time values and having null values.

[andriesb]

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]

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]

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