Analysing dates values placed in different rows

Marco_13112001

Good morning,

I'm trying to build a logic that will compare two date values and returns the comparison as a positive or negative number, the restriction is that the check has to be done within a group that have the same ID number. However, both date values are not on the same column or row (see example below).

The idea is to "flag" any start date that happens earlier than the end date of the previous row.

RECIPIENT_ID	PROVISION_DETAIL_START_DATE_DTTM	PROVISION_DETAIL_END_DATE_DTTM
17	01-Jul-2022 00:00:00.000Z[UTC]	02-Jul-2022 00:00:00.000Z[UTC]
17	01-Jan-2023 00:00:00.000Z[UTC]	31-Mar-2023 00:00:00.000Z[UTC]
17	01-Feb-2023 00:00:00.000Z[UTC]	03-Jul-2029 00:00:00.000Z[UTC]

I've used in the past the function compare date/time to perform the comparison but for values that were in the same row.

Is there any out of the box function that can be used on this case?

Many thanks in anticipation.

Josh Boxer

I would get the values you want to compare into the same row

From the example give it looks like assume you are picking the latest date from column 3 and the earliest date from column 6 (within ID 17)?

Use a Group step/action to group by 'ID' then aggregate column 3 Minimum and column 6 Maximum

Then use Compare date/time function you have used previously

=====

If it is always guaranteed to be in the previous row as stated then there is this similar question (that might not be all that performant for large volumes of data)

https://community.experianaperture.io/discussion/comment/1301#Comment_1301

Or this that might help to come up with a solution

https://community.experianaperture.io/discussion/comment/1556#Comment_1556

Marco_13112001

@Josh Boxer yes, it works. Many thanks again for your help