Replicate XLOOKUP with multiple lookup values and using 2 input data sets

Question

Hi Alteryx community,

In Alteryx Designer I am attempting to create a new column, FX Rate, in 1 data set that gives me the FX rate of the currency in the row on the date in the row. The trouble is, the FX rates are in a 2nd data set.

At first I thought I could just join the 2 data sets but that does not appear to work because the 1st data set has multiple records for each currency/date pair.

So normally, if the data sets were small enough, I would just use an XLOOKUP function in Excel like the screenshot below and be done with it (just imagine they are separate files and not in the same workbook), but in this case I am dealing with millions of records, so that is not an option.

I have tried reading How To: Replicate Excel XLOOKUP Function in Alteryx Designer but I really can't see how these techniques would help solve my problem. So, if anyone smarter than me (not so difficult) has any ideas, I would be super grateful to you 🙏

DataNath · Accepted Answer

Hey @DMaddy, what are you currently conducting your join on? Unless I'm missing something, to replicate the XLOOKUP functionality you're currently using, you'll just want to conduct a join between the 2 datasets based on [Price Currency] = [CURRENCY] and [Date] = [TIME_PERIOD]. Surely having multiple date/currency records is just part of your data?

DMaddy · Accepted Answer

Hey DataNath, so I just wanted to follow-up on this. You were right, the solution was as simple as using a Join, as I originally thought too. The problem actually was that I have several files of ECB FX rate data that I performed a union on and even though visually they all look like they are in dd/MM/yyyy format, it turns out looks can be deceiving. After some trial and error investigation I realized that for some reason, some of the records are also in yyyy-MM-dd format, even though they come from the same source and that is not what it looks like in Excel. So I had to perform two back-to-back DateTime String to Date/Time format conversions, write a Formula to consolidate the output of the conversions and THEN the Join worked like you said. Weird. But it's all good now.