Compare Event Data Efficiently

[jnovozhenets]

Hi - 

I have a large dataset containing events, event initiators, and event dates. I need to assign an importance metric to certain event initiator based on event dates. However, because I am comparing one event's date to the dates of all the other events in the dataset, the workflow becomes extremely computationally intensive.

For example:

Initiator	Date
1	1/1/22
2	1/17/22
3	1/17/22
4	2/24/22
5	4/30/22
6	5/20/22
7	4/11/23

From the above, I would want to find all dates within 30 days of initiator 1's date. Then I would move to initiator 2 and identify all dates within 30 days of initiator 2's date, etc. until all initiators have been checked. Date pairs are invalidated if there is an interceding date.

I'm looking for suggestions as to how to complete this task in an optimal way. Thanks!

[Yoshiro_Fujimori]

Hi @jnovozhenets ,

Batch Macro may be suitable for this case.

I assumed the output data you expect as below.

I hope this helps.

Input

Initiator	Date
1	2022-01-01
2	2022-01-17
3	2022-01-17
4	2022-02-24
5	2022-04-30
6	2022-05-20
7	2023-04-11

Output

Source_Initiator	Source_Date	Initiator in Range	Date in Range
1	2022-01-01	2	2022-01-17
1	2022-01-01	3	2022-01-17
5	2022-04-30	6	2022-05-20

Main Workflow

Batch Macro

Expression in Filter Tool

[Date] > "2022-01-01" AND
[Date] <= DateTimeAdd("2022-01-01" , 30, "day")

The date 2022-01-01 is updated with the value from Control Parameter Tool.