Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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