Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Kenneth,
You need to use the INTERVAL-function (see example below
LOAD
*,
INTERVAL (End_time -Start_time , 'D hh:mm:ss') as interval;
LOAD
@1 as Start_time,
@2 as End_time
FROM C:\Users\rfn7501\Documents\test.xls (biff, no labels, table is [Sheet1$]);
Then, if you need days and hours separately, you go on with the interval field, using left and right functions.
Regards,
Dear Johan,
please advice on how can i use the left and right function because i need days and time spent separately.
Thank you,
Kind Regards,
Kenneth
Hi Kenneth,
There's another way that is more sure, and that is Subfield. I've entered it into the example, so you can copy.
LOAD
*,
Subfield (interval,' ',1) as Hour_difference,
Subfield (interval,' ',2) as Minutes_difference;
LOAD
*,
INTERVAL(End_time-Start_time, 'D hh:mm:ss') as interval;
LOAD
@1 as Start_time,
@2 as End_time
FROM
C:\Users\rfn7501\Documents\test.xls(biff, no labels, table is [Sheet1$]);
What subfield does, is looking for a separator. In our case, this is a blank (' '), and then you define which part you need.
Regards,
Johan
Hello Kenneth,
Did my solution help you solve your issue? If so, could you then please close this question (mark it as answered)? If not, just let me know.
Kind regards,
Johan
Dear Johan,
im struggling on the subfield function ,I don't understand .what should I put within the ' ' (quotes).
or is there a way I can do this on the expression.
Thank you,
Kind regards,
Kenneth
Hello Kenneth,
If you take a look at the interval-function, then you'll see it is scripted 'D hh:mm:ss', where between D and hh, there is a space. This space you'll find back in your interval variable as well. (e.g. '2 00:12:12')
In order to separate the days (D) and the hours/minutes/seconds (hh:mm:ss), we will now use this space.
In the subfield-statement, you look for a unique separator, in this case the space, and you use that to 'cut' your variable.
Subfield (interval, ' ', 1) as Y actually reads like : take the variable interval, look for the separator 'space' (' '), and then return the first bit of information before the separator. So, between the brackets, you put a space (1 hit on your spacebar)
To make it more visible, you could script the interval function as 'D|hh:mm:ss'. The separator is then no longer a space, but a |. Your variable becomes e.g. 2|00:12:12. The subfield command then becomes Subfield(interval, '|',1).
Regards,
Johan
Dear Johan,
Thank you, i thought maybe i have to put something your help is much appreciated.
Kind regards,
Kenneth