topic Re: Hourly Time Intervals By Date in QlikView

Hourly Time Intervals By Date

mgreen76 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data basically structured like below :-

ItemID, Start, End, Duration (Mins)

a, 18/07/2018 21:52:00, 20/07/2018 02:17:00, 1705

What I need is to split up the datetime fields and create records for each hourly period along with the relevant duration for that hourly period.

So ending up with records such as

itemId, Date, HourStart, HourEnd, Duration

a, 18/07/2018, 21:00, 22:00, 8

a, 18/07/2018, 22:00, 23:00, 60

a, 18/07/2018, 23:00, 00:00, 60

a, 19/07/2018, 00:00, 01:00, 60

a, 19/07/2018, 01:00, 02:00, 60

a, 19/07/2018, 02:00, 03:00, 60

.........(record for each hour)......

a, 20/07/2018, 00:00, 01:00, 60

a, 20/07/2018, 01:00, 02:00, 60

a, 20/07/2018, 02:00, 03:00, 17

I'm guessing some sort of loop but can't get anything working at the moment. Any ideas?

Thanks in advance.

Re: Hourly Time Intervals By Date

qlikviewwizard — Tue, 07 Aug 2018 04:20:09 GMT

Hi mgreen76

Can you explain little bit more?

What is your source?

How you want to see?

Re: Hourly Time Intervals By Date

mgreen76 — Tue, 07 Aug 2018 09:05:38 GMT

Hi,

My source is basically as described above. I have a start datetime, end datetime, duration and item id.

The duration of an item can be over multiple days and I need to split that duration into hourly periods by day.

So using the original example data, between 01:00 and 02:00 on 20/07/18 60 mins would have been allocated, whereas between 02:00 and 03:00 on 20/07/18 only 17 mins would have been allocated.

Re: Hourly Time Intervals By Date

pooja_prabhu_n — Tue, 07 Aug 2018 10:39:19 GMT

HI,

try below code:

Please find the attached qvw file

T1:

LOAD * INLINE [

ItemID, Start, End, Duration (Mins)

a, 18/07/2018 21:52:00, 20/07/2018 02:17:00, 1705

];

T2:

NoConcatenate

LOAD ItemID,

Timestamp(Timestamp#(Start,'DD/MM/YYYY hh:mm:ss'),'MM/DD/YYYY hh:mm:ss') as Start,

Date(Timestamp#(Start,'DD/MM/YYYY hh:mm:ss'),'MM/DD/YYYY') as Start_date,

Timestamp(Timestamp#( Date(Timestamp#(Start,'DD/MM/YYYY hh:mm:ss'),'MM/DD/YYYY')

&' '&Timestamp(frac(Ceil(Timestamp#(Start,'DD/MM/YYYY hh:mm:ss'),1/24)),'hh:mm:ss'),'MM/DD/YYYY hh:mm:ss'),'MM/DD/YYYY hh:mm:ss')as start_rounded,

Timestamp(Timestamp#(End,'DD/MM/YYYY hh:mm:ss'),'MM/DD/YYYY hh:mm:ss') as End,

[Duration (Mins)]

Resident T1;

DROP Table T1;

MinMaxDate:

Load

min(start_rounded) as MinDate,

Max(End) as MaxDate,

ItemID

Resident T2

Group By ItemID;

Let vMinDate = PEEK('MinDate',0,'MinMaxDate');

Let vMaxDate = PEEK('MaxDate',0,'MinMaxDate');

Join(T2)

Load Timestamp(DateNum,'MM/DD/YYYY hh:mm:ss') as Start,

Date(Floor(DateNum),'MM/DD/YYYY') as Start_date;

Load

Timestamp($(vMinDate) + (IterNo()-1) / (24))as DateNum

AutoGenerate 1

While ($(vMinDate) + (IterNo()-1) / (24)) <= $(vMaxDate);

Time_interval:

NoConcatenate

LOAD if(len(trim(ItemID))=0,Peek('ItemID'),ItemID) as ItemID,

Start,

Start_date,

End,

Hour(Start) as HourStart,

Hour(Start)+1 as HourEnd,

60-Num#(Timestamp(Start,'mm'))as Duration,

[Duration (Mins)]

Resident T2 ;

DROP Table T2;

DROP Table MinMaxDate;

Re: Hourly Time Intervals By Date

bwisealiahmad — Tue, 07 Aug 2018 10:47:15 GMT

Hi,

So with the example of this row:

a, 18/07/2018 21:52:00, 20/07/2018 02:17:00, 1705

You can do the following in your script:

Temp_Data:

LOAD * Inline [

ItemID, Start, End, Duration (Mins)

A, 18/07/2018 21:52:00, 20/07/2018 02:17:00, 1705

];

Data:

NoConcatenate

LOAD

Interval(HourStartTime - HourEndTime,'hh') AS Difference

;

LOAD

ItemID,

Date#(Left(Start,10),'DD/MM/YYYY') AS Date,

Hour(Timestamp#(Start,'DD/MM/YYYY hh:mm:ss')) AS HourStart,

Hour(Timestamp#(End,'DD/MM/YYYY hh:mm:ss')) AS HourEnd,

Time(Time#(Start,'DD/MM/YYYY hh:mm:ss'),'hh:mm') AS HourStartTime,

Time(Time#(End,'DD/MM/YYYY hh:mm:ss'),'hh:mm') AS HourEndTime

Resident Temp_Data;

Drop Table Temp_Data;

Front-end example:

Is that how you want it?

Or do you want the difference to be 5 in this case? Since it is 5 hours from 21:00 to 02:00 AM

Best,

Ali A

Re: Hourly Time Intervals By Date

mgreen76 — Tue, 07 Aug 2018 15:37:57 GMT

Hi,

This doesn't work if I have more than one row of data feeding in from the initial table eg :-

T1:

LOAD * INLINE [

ItemID, Start, End, Duration (Mins)

a, 18/07/2018 21:52:00, 20/07/2018 02:17:00, 1705

b, 17/07/2018 22:52:00, 19/07/2018 03:17:00, 1705

];

I don't get a duration for each hour per ItemID.

Re: Hourly Time Intervals By Date

mgreen76 — Tue, 07 Aug 2018 15:40:34 GMT

I actually want more like what had been suggested above from Pooja byt allowing for multiple ItemID's with differing start and end timestamps.

Re: Hourly Time Intervals By Date

MarcoWedel — Tue, 07 Aug 2018 22:14:36 GMT

Hi,

one solution could be:

table1:
LOAD ItemID,
    DayName(HourStart) as Date,
    Time(Frac(HourStart),'hh:mm') as HourStart,
    Time(Frac(HourEnd),'hh:mm') as HourEnd,
    Round((HourEnd-HourStart)*1440) as Duration;
LOAD ItemID,
    Timestamp(RangeMax(Floor(Start,'01:00')+'01:00'*(IterNo()-1),Start)) as HourStart,
    Timestamp(RangeMin(Floor(Start,'01:00')+'01:00'*IterNo(),End)) as HourEnd
Inline [
    ItemID, Start, End
    a, 18/07/2018 21:52:00, 20/07/2018 02:17:00
]
While Floor(Start,'01:00')+'01:00'*(IterNo()-1) <= End;

hope this helps

regards

Marco

Re: Hourly Time Intervals By Date

pooja_prabhu_n — Wed, 08 Aug 2018 06:49:04 GMT

Hi,

try the below code to fetch for each ItemID

MinMaxDate:

Load

min(start_rounded) as MinDate,

Max(End) as MaxDate,

ItemID

Resident T2

Group By ItemID;

LET vRowCount = NoOfRows('MinMaxDate');

FOR i = 1 TO $(vRowCount)

LET vID=Num#(Peek('ItemID',$(i)-1,'MinMaxDate'));

Let vMinDate = PEEK('MinDate',$(i)-1,'MinMaxDate');

Let vMaxDate = PEEK('MaxDate',$(i)-1,'MinMaxDate');

Join(T2)

Load Timestamp(DateNum,'MM/DD/YYYY hh:mm:ss') as Start,

Date(Floor(DateNum),'MM/DD/YYYY') as Start_date;

Load

Timestamp($(vMinDate) + (IterNo()-1) / (24))as DateNum

AutoGenerate 1

While ($(vMinDate) + (IterNo()-1) / (24)) <= $(vMaxDate);

NEXT i;