I know how to do traditional joins, where you join on two values being equal, but what about if you want to join on the value that is closest to the one you have?
I have a function in MySQL which returns me a value from the row with the closest timestamp to the one I provide to it.
We are interested in moving some functions into talends logic and I was wondering if it was possible to do this kind of thing?
Good luck. This is quite fiddly, but once you have figured this out, it is a very useful technique to help solve lots of different problems. One thing I forgot to mention is that the tMap variables are processed in order (from top to bottom). This logic (if you follow the tutorial) is based around comparing the value of the next tMap variable in the computation of the tmap variable above. This is how you can take a peak back at the last row value. Once the variable above has been set, the variable you were looking back at is updated to be the current row variable ready for the next row's "look back". It is quite tricky to explain (hence the long winded explanation) but really useful and not many people are aware of it.
Performing logic like this inside a database will always be quicker. If all your data is in the same database and you want performance to be optimal, I would always advocate carrying out logic like this in the database. Otherwise you will incur the latency of retrieving the data from the database, network performance, then processing the data in memory in Java. I assumed that the data was coming from different sources when I answered this. It was also an interesting problem to look at 🙂
