Exchange Rate match when date is in date range

Question

Hi,

I have a file of sales information and another file of exchange rate with effective date range.

For the sales table, if the invoice date is within the effective date range and the currency code match the currency conversion table, I'll use that exchange rate to convert my sales to USD.

Does anyone know how I can do this effectively? There's no rule to setting up valid dates. That's just what another department gave me every time they have a change.

Sales table:

Invoice dateCurrency codeAmount9/28/2019USD3509/1/2019CNY23510/1/2019AED351

Currency conversion table:

0.27229AED09/27/201910/2/20190.01747ARS09/27/201910/2/20190.24023BRL09/27/201910/2/20190.75442CAD09/27/201910/2/20191.00778CHF09/27/201910/2/20190.14034CNY09/27/201910/2/20190.27229AED08/30/20199/26/20190.01709ARS08/30/20199/26/20190.24075BRL08/30/20199/26/20190.75199CAD08/30/20199/26/20191.01141CHF08/30/20199/26/20190.13985CNY08/30/20199/26/2019

Thank you!

RolandSchubert · Accepted Answer

Hi @LinhNguyen ,

I think, the easiest solution will be to create a table using thre Generate Rows tool. This table should have one entry for each day and currency, so you can join by date and currency with the invoice table. See attached workflow how to create this join table.

Best regards

Roland