Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mgreen76
Contributor
Contributor

Hourly Time Intervals By Date

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.


8 Replies
qlikviewwizard
Master II
Master II

Hi mgreen76

Can you explain little bit more?

What is your source?

How you want to see?

mgreen76
Contributor
Contributor
Author

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
Creator III
Creator III

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;


time.PNG

bwisealiahmad
Partner
Partner

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:

Capture.PNG

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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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:

QlikCommunity_Thread_310174_Pic1.JPG

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
Creator III
Creator III

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;