Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I hope this mail finds you well.
I would like to recreate each row per day (screenshot below) and excel file in attachment:
What is the best approach?
Thank you very much for your help.
Kind regards,
Nicolas.
Use a WHILE clause in your LOAD statement like shown in
Thank you for your return Stefan. With the script below, the final result is not as expected (hour start and hour end):
Duration:
LOAD Vehicle,
date("From" + IterNo() -1) as Date,
timestamp("From" + IterNo() -1) as "From",
timestamp("To" + IterNo() -1) as "To"
While IterNo() <= "To" - "From"+ 1 ;
LOAD
Vehicle,
"From",
"To",
Duration
FROM [lib://Data_Connection/Duration.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Raw Data]);
Thanks for your help.
Kind regards,
Nicolas.
You can do it similar to what is discussed here:
Calculate hours between two Date/Time strings
In your case without business hours, it makes things easier:
INPUT:
LOAD Vehicle,
From,
To,
Duration
FROM
(ooxml, embedded labels, header is 1 lines, table is [Raw Data]);
PerDate:
LOAD *, Interval(End-Start) as DurationDate;
LOAD Vehicle,
Dayname(From+iterno()-1) as Date,
Time(if(iterno()=1, frac(From),0)) as Start,
Time(if(daystart(From)+iterno()-1=daystart(To), frac(To),1)) as End
Resident INPUT
while daystart(To) >= daystart(From)+iterno()-1;
Hello Stefan,
Thanks for your return.
IIt works with the script below but if we have multiples dates, I would like to take the end of the day (ex. End_Date_Time= 30/07/2018 23:59:59 and not 31/07/2018 00:00:00)
Temp0:
LOAD RowNo() as ID,
RowNo() & '|' & Vehicle as Key,
Vehicle,
From as Start_Date_Time,
To as End_Date_Time,
Duration
FROM [lib://Data_Connection/Duration.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Raw Data]) ;
NoConcatenate
Temp:
load * resident Temp0 order by Start_Date_Time asc;
NoConcatenate
Final:
Load
*,
Interval(End_Date_Time-Start_Date_Time,'hh:mm:ss') as Duration,
Date(Floor(Start_Date_Time)) as [Date For Calendar];
Load
Key
,Vehicle
,If(Key = Peek(Key), TimeStamp(Floor(Start_Date_Time) + IterNo() - 1), Start_Date_Time) as Start_Date_Time
,If(Floor(Start_Date_Time) + IterNo() < End_Date_Time, TimeStamp(Floor(Start_Date_Time) + IterNo()), End_Date_Time) as End_Date_Time
Resident Temp
While Floor(Start_Date_Time) + IterNo() - 1 < End_Date_Time;
Drop Tables Temp,Temp0;
Drop Field Key;
Thank you for your help
KR
Nicolas.
It might be enough to subtract approx. a millisecond (in my example, 0.00000001 as fraction of a day) from the End_Date_Time in case of multiple dates:
,If(Floor(Start_Date_Time) + IterNo() < End_Date_Time, TimeStamp(Floor(Start_Date_Time) + IterNo()-1E-8), End_Date_Time) as End_Date_Time
Thank you Stefan.
But now the total duration is not exact (truncated) (cf. Excel file and qvf in attachment)
Thanks. !
KR,
Nicolas.
You can round to full second where you aggregate the single durations.
Interval(Round(Sum(Duration),Maketime(0,0,1)))