Filling dates between start and end date

[nicogene]

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.

[swuehl]

Use a WHILE clause in your LOAD statement like shown in

Creating Reference Dates for Intervals

[nicogene]

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.

[swuehl]

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;

[nicogene]

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.

[swuehl]

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

[nicogene]

Thank you Stefan.

But now the total duration is not exact (truncated) (cf. Excel file and qvf in attachment)

Thanks. !

KR,

Nicolas.

[swuehl]

You can round to full second where you aggregate the single durations.

Interval(Round(Sum(Duration),Maketime(0,0,1)))