Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to make a heat map to count how many tasks are running in each time period.
Source Data:
Table1
Name |
Start time |
End time |
Task1 |
3/23/2023 7:00 |
3/23/2023 7:29 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:09 |
Task2 |
3/23/2023 7:00 |
3/23/2023 7:05 |
Task2 |
3/24/2023 7:30 |
3/24/2023 7:47 |
Task3 |
3/23/2023 7:19 |
3/23/2023 7:33 |
Task3 |
3/24/2023 7:15 |
3/24/2023 7:29 |
Expected result:
HeatMap:
|
7:00 |
7:15 |
7:30 |
7:45 |
8:00 |
8:15 |
8:30 |
8:45 |
9:00 |
3/23/2023 |
1 |
3 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
3/24/2023 |
|
2 |
2 |
2 |
1 |
1 |
1 |
1 |
1 |
---------------------------------------------------------------------------------------------------------------------------
I have tried the following to process the data
1.Create a new table with field Time_Tag. And use "IntervalMatch" but failed to join
Table2
Time_Tag |
7:00 |
7:15 |
7:30 |
7:45 |
8:00 |
8:15 |
8:30 |
8:45 |
9:00 |
2.Join the table by IntervalMatch to get below table, but there is no change for Table1
Inner Join IntervalMatch (Time_Tag)
load Start_time, End_Time
Resident Table1;
Actual result:
Name |
Start time |
End time |
Task1 |
3/23/2023 7:00 |
3/23/2023 7:29 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:09 |
Task2 |
3/23/2023 7:00 |
3/23/2023 7:05 |
Task2 |
3/24/2023 7:30 |
3/24/2023 7:47 |
Task3 |
3/23/2023 7:19 |
3/23/2023 7:33 |
Task3 |
3/24/2023 7:15 |
3/24/2023 7:29 |
Expected result:
MatchTable(Table1😞
Name |
Start_time |
End_time |
Time_Tag |
Task1 |
3/23/2023 7:00 |
3/23/2023 7:29 |
7:00 |
Task1 |
3/23/2023 7:00 |
3/23/2023 7:29 |
7:15 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
7:15 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
7:30 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
7:45 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
8:00 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
8:15 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
8:30 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
8:45 |
Task1 |
3/24/2023 7:15 |
3/24/2023 9:05 |
9:00 |
Task2 |
3/23/2023 7:00 |
3/23/2023 7:05 |
7:15 |
Task2 |
3/24/2023 7:30 |
3/24/2023 7:47 |
7:30 |
Task2 |
3/24/2023 7:30 |
3/24/2023 7:47 |
7:45 |
Task3 |
3/23/2023 7:19 |
3/23/2023 7:33 |
7:15 |
Task3 |
3/23/2023 7:19 |
3/23/2023 7:33 |
7:30 |
Task3 |
3/24/2023 7:15 |
3/24/2023 7:29 |
7:15 |
Is there something wrong with my approach, or is there a better way of doing this?
try this:
Table1: Name,start_time,end_time,start_time-date(floor(start_time)) as S_time,end_time-date(floor(start_time)) as E_time from source;
Table2:
Load time(time#(time_tag,'hh:mm'),'hh:mm') as time_tag from source;
join(Table1)
interval match(time_tag)
load S_time,E_time resident Table 1;
Drop Table 2;
There can be multiple reason for your script to not work like it might not get the time of time_tag,
if your start time and end time is in format like hh:mm:ss but it is only showing hh:mm then my expression wont work.
try this:
Table1: Name,start_time,end_time,start_time-date(floor(start_time)) as S_time,end_time-date(floor(start_time)) as E_time from source;
Table2:
Load time(time#(time_tag,'hh:mm'),'hh:mm') as time_tag from source;
join(Table1)
interval match(time_tag)
load S_time,E_time resident Table 1;
Drop Table 2;
There can be multiple reason for your script to not work like it might not get the time of time_tag,
if your start time and end time is in format like hh:mm:ss but it is only showing hh:mm then my expression wont work.
Hi Akash,
Thank you for your answer. But there is still a some issue:
I tried as you said but End_time can't be matched by IntervalMatch:
//In Table1:
time(hour(Most_Recent_Run_Start_Time)&':'&Floor(Minute(Most_Recent_Run_Start_Time),15),'hh:mm') as Start_Time_15,
time(time(Most_Recent_Run_Start_Time) + (Ceil(Most_Recent_Run_Time_In_Seconds/60)/(24*60)),'hh:mm') as End_Time
//In Table2:
time(time#(Time_Tag,'hh:mm'),'hh:mm') as Time_Tag
//Join
Join (Table1)
IntervalMatch (Time_Tag)
load Start_Time_15,End_Time
Resident Table1;
Could you please kindly advise?
Thanks & Regards
Haynes
Hi,
another example using intervalmatch might be:
Table1:
LOAD *,
Timestamp#(Timestamp(Floor([Start time],'00:15:00'),'MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') as StartTimePeriod,
Timestamp#(Timestamp(Floor([End time],'00:15:00'),'MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') as EndTimePeriod;
LOAD Name,
Timestamp#([Start time],'MM/DD/YYYY hh:mm') as [Start time],
Timestamp#([End time],'MM/DD/YYYY hh:mm') as [End time]
Inline [
Name, Start time, End time
Task1, 3/23/2023 7:00, 3/23/2023 7:29
Task1, 3/24/2023 7:15, 3/24/2023 9:09
Task2, 3/23/2023 7:00, 3/23/2023 7:05
Task2, 3/24/2023 7:30, 3/24/2023 7:47
Task3, 3/23/2023 7:19, 3/23/2023 7:33
Task3, 3/24/2023 7:15, 3/24/2023 7:29
Task4, 3/24/2023 8:33, 3/24/2023 12:17
Task4, 3/25/2023 6:44, 3/25/2023 11:05
Task5, 3/23/2023 9:59, 3/23/2023 10:01
Task5, 3/24/2023 12:59, 3/24/2023 15:01
Task6, 3/27/2023 18:20, 3/29/2023 3:10
Task7, 3/23/2023 7:00, 3/23/2023 7:01
Task8, 3/23/2023 8:01, 3/23/2023 8:02
Task9, 3/23/2023 9:00, 3/23/2023 9:00
];
Table2:
LOAD *,
Time(Frac(TimePeriod),'hh:mm') as Time_Tag,
Date(Floor(TimePeriod),'MM/DD/YYYY') as Date_Tag;
LOAD Timestamp#(Timestamp(MinTimePeriod+(IterNo()-1)*'00:15:00','MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') as TimePeriod
While Timestamp#(Timestamp(MinTimePeriod+(IterNo()-1)*'00:15:00','MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm')<MaxTimePeriod;
LOAD Floor(Min(StartTimePeriod)) as MinTimePeriod,
Ceil(Max(EndTimePeriod)) as MaxTimePeriod
Resident Table1;
Left Join (Table1)
IntervalMatch (TimePeriod)
LOAD StartTimePeriod, EndTimePeriod
Resident Table1;
Ok, then you need to floor Down all the data to hours and minutes,
try creating a new field where it is just time and not dates like this:-
Time(Floor(Num(Start_Time)-Num(Floor(StartTime)),1/(24*60)),'hh:mm') as S_time, It will remove all the date and keep hours and minutes, Do the same with End_time and then try interval match.
I subtracted Floored Dates to get just hours, the normal floor function would always floor to a base numerical value, that why I gave an Offset of 1/(24*60).
I have tried converting the time to a number, and found that Num(Start_Time) and Num(Time_Tag) is less than 1 while Num(End_Time) is more than 4000.
The function "IntervalMatch" is working after doing below change:
time(left(End_Time,5) ,'hh:mm')
Thank you
Hi MarcoWedel,
Many thanks for your answer. But the variable TimePeriod is not found.
I solved this problem with another way.
Thanks & Regards