topic Timestamp subtraction in QlikView

Timestamp subtraction

Thu, 19 Jan 2012 10:52:10 GMT

Hi Guys,

I am loading from excel workbook

I have the following data

Start Time Start Date End Time End Date

9:00:00 01/01/2012 10:00:00 02/01/2012

10:00:00 01/01/2012 10:00:00 05/01/2012

My Formats are as follows :

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

I wish to load add a field called Turnaround which id the dfferent in Hours and Minutes between the 2

Start Time Start Date End Time End Date Turnaround

9:00:00 01/01/2012 10:00:00 02/01/2012 25:00

10:00:00 01/01/2012 10:30:00 05/01/2012 96:30

I have tried a few different versions on the cript but all to no avail

Can anyone advise please ?

Thanks

Anne

swuehl — Thu, 19 Jan 2012 11:43:23 GMT

I assume your Date and Time fields will be correctly read in, since your DateFormat and TimeFormat seems to match.

Then you could use interval() function in your load like this:

LOAD

interval( (EndDate+EndTime) - (StartDate+StartTime) ) as Turnaround;

LOAD

...

StartTime,

StartDate,

EndTime,

EndDate,

from ExcelTable.xls;

Thu, 19 Jan 2012 12:52:25 GMT

Perfect , you make it look so easy Swuehl,

Thanks