"IntervalMatch" is not working

[Haynes_FengQH]

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?

[Gabbar · Accepted solution]

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.

[Haynes_FengQH]

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

[MarcoWedel]

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;

[Gabbar]

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).

[Haynes_FengQH]

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

[Haynes_FengQH]

Hi MarcoWedel,

Many thanks for your answer. But the variable TimePeriod is not found.

I solved this problem with another way.

Thanks & Regards