Knime find a match of a value for first column in second column, within multiple rows

[chaithuj]

Hi All,

I am stuck almost whole day and need to deliver this end of the day. Any help in figuring out the logic is very helpful.

I have employee whose pay dates for year 2023 are available in Pay_date column.
I have to calculate his pay_date from his first contribution date.

So I need an output like this

The logic is for each SSN :
Determine first pay date, using the date that is on-or-before the first contribution date

How do i do and get single row out of it. Group by doesn’t have option to use a condition
In this example it is the last Pay_date, but it could be anywhere in the middle or sometimes it could be the first pay date too

[ArjenEX]

Hi @chaithuj

Your example shows all unique SSN’s and the expected output you posted looks like the same as the input. Can you please double check?

I think you mean that the SSN is the same throughout all the rows and you’re looking for the record whereby the first_cont_date and the Pay_Day are the closest?

Furthermore, how are those dates formatted? Are they string or of type date?

[chaithuj]

yeah right SSN is same and I am trying to find the pay date that is equal to or just before that first contribution date

[ArjenEX]

Then you can calculate the difference with a Date&Time Difference node.

Then sort on the difference per SSN.

Groupby based on the SSN and use first as aggregation method for both date columns.

[chaithuj]

Thanks a lot @ArjenEX , you saved me

[chaithuj]

worked perfectly and Thanks a ton @ArjenEX