Count number of occurences within XX hours per unit

[flemmingbj]

I need to find the number of instances where a given car has two arrival times within 24 hours from the previous arrival.

In the table car A has 1 (bold) and car B has 2 (all within 24 hours of previous arrival).

So I need to compare the arrival time per car and count the number of times this occurs.

What code is needed in the load script to count this? 

CAR	Arrival time
A	22-02-2019 07:30
A	22-02-2019 09:30
A	24-05-2019 07:30
B	26-02-2019 07:30
B	26-02-2019 11:30
B	26-02-2019 12:30
C	28-02-2019 07:30
C	01-05-2019 07:30
C	02-08-2019 07:30

[sunny_talwar]

Try this script

Table:
LOAD * INLINE [
CAR, Arrival time
A, 22-02-2019 07:30
A, 22-02-2019 09:30
A, 24-05-2019 07:30
B, 26-02-2019 07:30
B, 26-02-2019 11:30
B, 26-02-2019 12:30
C, 28-02-2019 07:30
C, 01-05-2019 07:30
C, 02-08-2019 07:30
];

FinalTable:
LOAD CAR,
[Arrival time],
If(CAR = Previous(CAR), If(Floor([Arrival time]) = Floor(Previous([Arrival time])), 1, 0), 0) as [Arrival Repeat Flag]
Resident Table
Order By CAR, [Arrival time];

DROP Table Table;

To get this

Now you can just sum the new flag.

[flemmingbj · Accepted solution]

I think that works - simple and effective 🙂

Thanks!