Compare previous values in one column with next value on another column

Hi everyone,

I’m analysing flight data, and I need to calculate the flights available and those that became unavailable by day.

In the data, I have a unique key that represents the airport, the departure, arrival date and the timestamp. First I used the lag column with the lag of -1. And now I need to understand, if the unique key (not lag) in one column is equal to the key (lagged).

from_loc to_loc departure date arrival date timesptamp uniquekey uniquekey lagged
BJL BRU 4/12/2022 4/12/2022 2022-02-14T16:13 BJLBRU2022-04-122022-04-12 ?
BJL AMS 3/2/2022 3/2/2022 2022-02-14T16:13 BJLAMS2022-03-022022-03-02 BJLBRU2022-04-122022-04-12
BJL AMS 3/23/2022 3/23/2022 2022-02-14T16:13 BJLAMS2022-03-232022-03-23 BJLAMS2022-03-022022-03-02
AYT BRU 10/16/2022 10/16/2022 2022-02-14T16:13 AYTBRU2022-10-162022-10-16 BJLAMS2022-03-232022-03-23
AYT BRU 10/22/2022 10/22/2022 2022-02-14T16:13 AYTBRU2022-10-222022-10-22 AYTBRU2022-10-162022-10-16
AYT BRU 10/28/2022 10/28/2022 2022-02-14T16:13 AYTBRU2022-10-282022-10-28 AYTBRU2022-10-222022-10-22
AYT BRU 3/3/2022 3/3/2022 2022-02-14T16:13 AYTBRU2022-03-032022-03-03 AYTBRU2022-10-282022-10-28
AYT BRU 4/1/2022 4/1/2022 2022-02-14T16:13 AYTBRU2022-04-012022-04-01 AYTBRU2022-03-032022-03-03
AYT BRU 4/7/2022 4/7/2022 2022-02-14T16:13 AYTBRU2022-04-072022-04-07 AYTBRU2022-04-012022-04-01
AYT BRU 4/13/2022 4/13/2022 2022-02-14T16:13 AYTBRU2022-04-132022-04-13 AYTBRU2022-04-072022-04-07
AYT BRU 4/19/2022 4/19/2022 2022-02-14T16:13 AYTBRU2022-04-192022-04-19 AYTBRU2022-04-132022-04-13

Can someone help me on how to do this? I tried rule engine but not getting anywhere.

Thank you :wink:

What exactly did you try? And what was the result?

Rule Engine Provides the “=” comparison (as well as LIKE and Matches if you need wildcards or regex) but in your case the “=” should give you your True / False (or whatever you like to return for a match/mismatch)
br

Hi,

Will the uniquekey ever match the uniquekey lagged? I’m wondering since the date is appended and the examples you provide don’t show any matches. If they never match due to the date, you will need to remove the date info from each string by manipulating the string or regex.

If you need more help, please attach data, workflow, and expected outcome. Thank you~

Hello @AdrianaFerro
If I understand it correctly, you want to compare the first 6 characters ‘upper case’ from the two most right hand side columns ($uniquekey$, $uniquekey lagged$).

There are few approaches, like two String Manipulation nodes extracting the 6 characters and compare the resulting columns with a ‘Rule Engine’ node …

String Manipulation 1
substr($uniquekey$, 0, 6)
String Manipulation 2
substr($uniquekey lagged$, 0, 6)

Rule Engine:

$new column1$ LIKE $new column2$ => TRUE
TRUE => FALSE

but if you want the most straight forward and efficient approach, regex compare is a one step node:

String Manipulation:

regexMatcher(
	regexReplace($uniquekey$, "^(\[A-Z]{6}).*", "$1")
	,  regexReplace($uniquekey lagged$, "^(\[A-Z]{6}).*", "$1")
)

BR

@AdrianaFerro

Sorry if I am being to simplistic , probably not following you.
Don´t a join solve the issue?

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.