Matching multiple columns and rows

[Finnn]

Hello,

I am a pretty new Alteryx user but I already love working with it. I have a pretty specific problem for which I could not find an answer yet.

I have two files, one containing transaction data and the other one containing exchanges rates for multiple currencies for every day of the year. The transaction data is structured as follows (columns): transaction amount / transaction date / currency used. I now need to know the value of each transaction in EUR at the transaction date.

Therefore I need to match the entries in my FX-file with the transaction data based on transaction date, but also based on currency used. I had no problem matching the when only looking at one specific day using the join tool, but now I have to do this for transactions covering an entire year. It is also somewhat important that the solution is not too inefficient since the transaction data file contains several million rows and the fx-file every major currency for every day of the year.

Attached you can find a very rough example of the two tables, if you need tables that are closer to the "real ones" I will use I can do that but would need to do some work first.

Thanks a lot in advance!

[Luke_C · Accepted solution]

Hi @Finnn 

I think something like this would get you what you need.. looks like only 2 records agree between your sample data:

1. Transpose second table so dates become rows
2. Update data types for join
3. Join on currency & date

[RolandSchubert · Accepted solution]

Hi @Finnn ,

you can use  the Join  tool to match on multiple fields, but I think a bit of preparation is needed:

 

FX-rates are provided in rows for currencies and columns for dates. You have to use a Transpose tool to bring dates also to rows. The dates have to be converted to a date data type to enable the Join tool to work. 

After these preparation steps, you can join you transaction data and your FX-rates. Some records are in EUR, this seems to be the base currency, so you should set the FX-rate to 1 for these rows. Finally, union all records and you are ready to calculate. 

I've attached the workflow., hope, this is helpful.

Best,

Roland

[Finnn]

This is exactly what I needed, thanks a lot!

[Finnn]

Thanks, this is exactly what I needed!