Matching date based currency values from different tables

Hey all,
I have two different tables, where I want to match data by their date values. I have a simple order table looking like this:

Article ID Price Quantity Date Currency
123 1,54 1000 2019-11-04 ??
456 2,23 2000 2019-03-12 ??
789 4,55 1500 2019-08-25 ??

And I have a separate table with downloaded currency values,

Date Currency
2019-01-01 14,11
2019-02-01 14,22
2019-03-01 15,33
2019-04-01 15,87
2019-05-01 15,51
2019-06-01 15,23
2019-07-01 15,05
2019-08-01 14,98
2019-09-01 15,02
2019-10-01 14,91
2019-11-01 14,88
2019-12-01 14,89

Which routine takes the date value from the first table (order table) and looks up in the second table which currency value does apply according the selected date?

Hi @ochaas

Since the order dates are not exactly matching with the currency dates, does it mean that 2019-11-04 should be joined to 2019-11-01 since the order date is in November?

If that’s the case, I would opt to use the Extract Date&Time Fields node to extract the year and month (number) on both inputs.

Once you have this, you can use a Joiner node to find the corresponding currency for that month by matching on year and month.

Hope this helps!

1 Like

Hi Arjen, yes it works perfectly. Thanks for checking the dates, that they don’t match exactly and need a re-format first.
The accuracy of your solution approach is simply outstanding. Thank you so much for your support!!

1 Like

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