Using Multi-Row Formula

[Lazzanova]

I need to do some data quality check on a table which looks like this:

These are vehicles entering a compound, and are sorted on their timestamps. (Timestamp column not show here)
Ideally a vehicle enters a compound and then exits it. However there are multiple cases as you can see, where a car enters multiple times.
I need to identify these cases.

a vehicle_instance_id should have a Compound gate-in, followed by a row with Compound-gate-out.
It should not have multiple Compound Gate-in rows after one another.

How can I use the Multi-Row Formula, to identify these cases?

Wtih kind regards,

Lazzanova

[Luke_C · Accepted solution]

Hi @Lazzanova 

Here's one approach, I input some fake data based on your screenshot:

1. New field, grouped by the vehicle ID
2. Set rows that don't exist to NULL (i.e. row-1 for the first instance)
3. Do a check to see if the fact in the current row is different the fact in the previous row (i.e. exit vs enter)
4. If the previous row is null (from step 2), the formula will return true.
5. If the previous row for the vehicle ID is the same as the current row, it will show false. You can then filter on that.