Hourly Time Intervals By Date

[mgreen76]

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.

[qlikviewwizard]

Hi mgreen76

Can you explain little bit more?

What is your source?

How you want to see?

[mgreen76]

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.

[pooja_prabhu_n]

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;

[bwisealiahmad]

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

[mgreen76]

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.

[mgreen76]

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

[MarcoWedel]

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

[pooja_prabhu_n]

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;