Matching date based currency values from different tables

[ochaas]

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?

[ArjenEX]

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!

[ochaas]

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!!