topic Filling dates between start and end date in QlikView

Filling dates between start and end date

nicogene — Sat, 01 Sep 2018 11:44:17 GMT

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.

Re: Filling dates between start and end date

swuehl — Sat, 01 Sep 2018 14:35:12 GMT

Use a WHILE clause in your LOAD statement like shown in

Creating Reference Dates for Intervals

Re: Filling dates between start and end date

nicogene — Sat, 01 Sep 2018 16:44:16 GMT

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.

Re: Filling dates between start and end date

swuehl — Sun, 02 Sep 2018 07:53:28 GMT

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;

Re: Filling dates between start and end date

nicogene — Mon, 03 Sep 2018 10:00:31 GMT

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

Nicolas.

Re: Filling dates between start and end date

swuehl — Mon, 03 Sep 2018 10:35:05 GMT

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

Re: Filling dates between start and end date

nicogene — Mon, 03 Sep 2018 17:42:04 GMT

Thank you Stefan.

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

Thanks. !

KR,

Nicolas.

Re: Filling dates between start and end date

swuehl — Mon, 03 Sep 2018 17:56:31 GMT

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

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