Join the first row of data

Hello Everyone.

I have 2 tables:

ID       Time of Creation                                           

01       02.02.2016 16:30:00                                                                           

02       03.02.2016 15:30:00

03       02.05.2016 11:35:00

04      02.02.2016 14:22:00

And the second one which is login log :

Id      TimeOfLogin                          Version

01    04.02.2016 13:30:00              A

01    01.02.2016 11:30:00              B

01    01.01.2016 13:30:00              A

02       03.02.2016 13:30:00           A

02       03.02.2016 19:30:00           A

 

What i would like to do is to join every row from table A which is `Time of Creation` > `TimeOfLogin` but just to get one first row . just by first predicate expression i would get : 

01       02.02.2016 16:30:00   -  01    01.02.2016 11:30:00      B

01       02.02.2016 16:30:00   -  01    01.01.2016 13:30:00      A

 

and i just want that first row (and not the second) .

The reason for this is to know when user created task (table1) which Version user to do this (table2)

I hope its clear  unfortunatly can not attach example flow. 

 

 

Hi,

this more advanced join criteria are currently not avaiable with our Joiner node.

Therefore you need to first make a full Join, maybe on your id. And than filter the ones where your condition mets with the Rule-Based Row Filter.

Best regards, Iris

Iris thank for info.

Any tips how should i use rule-Based row Filter in my case ?

maybe something in predicate Expression in join node (from Actian Dataflow group) could work ?

 

basically i realized that what i want to do is to stop joining after first founded and joined row.

 

 

try using a loop

- table row to variable loop to convert each time of creation into a loop variable

- append the time of creation as an added constant value to the login and version data table

- convert any time strings to time, so you can compute the time difference between each login time and the particular creation time being looped

- filter out the later ones, and sort the remaining so the most recent is top

- row sample the top 1

- tidy up and collect in a loop end

 

have a look at the attached workflow, should do what you're after