Analysing dates values placed in different rows
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.
Best Answer
-
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)
Or this that might help to come up with a solution
1
Answers
-
@Josh Boxer yes, it works. Many thanks again for your help
1