Complex VLOOKUP

[martincarly]

Hi guys,

I’m struggling with a more complex lookup task. Have searched the forums, but I dont think cell replacer or Joiner node will help here?

Here’s what I want to achieve:

I have a list of transcations with multiple transactions per date, including multiple currencies.

I have a second list with one column per currency, and rows of historic exchange rates per date.

I want to add the correct exchange rate per currency and date to the first list of transactions for later recalculation.

Notes for the screenshot: the date in the first table is already formatted correctly in a later column. For EUR conversion I will ad a fixed column with only “1”.

Firsts data table

Second data table

Help is appreciated a lot!

Best
Martin

[ScottF]

I think if you unpivot your second table, so that you have one combination of date and currency per row, then it becomes a simple join problem.

(Unless I’m misunderstanding something.)

[ScottF]

And, I should have said - welcome to the forum!

Take a look at the Unpivoting node, and if you are still having trouble, please feel free to post a couple of small example datasets for us to use in engineering a solution.

[martincarly]

Thanks Scott - that worked! I didn’t know the unpivot before, thanks for your help!