Exchange Rate match when date is in date range

[LinhNguyen]

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 date	Currency code	Amount
9/28/2019	USD	350
9/1/2019	CNY	235
10/1/2019	AED	351

Currency conversion table:

0.27229	AED	09/27/2019	10/2/2019
0.01747	ARS	09/27/2019	10/2/2019
0.24023	BRL	09/27/2019	10/2/2019
0.75442	CAD	09/27/2019	10/2/2019
1.00778	CHF	09/27/2019	10/2/2019
0.14034	CNY	09/27/2019	10/2/2019
0.27229	AED	08/30/2019	9/26/2019
0.01709	ARS	08/30/2019	9/26/2019
0.24075	BRL	08/30/2019	9/26/2019
0.75199	CAD	08/30/2019	9/26/2019
1.01141	CHF	08/30/2019	9/26/2019
0.13985	CNY	08/30/2019	9/26/2019

Thank you!

[RolandSchubert · Accepted solution]

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

[LinhNguyen]

Thank you!