Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
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
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.
I actually want more like what had been suggested above from Pooja byt allowing for multiple ItemID's with differing start and end timestamps.
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
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;