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

[DMaddy]

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 solution]

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]

Hi DataNath thanks for the quick reply and thanks for pointing that out, because looking at it again I do see that the join does work in certain places, even with repeat date/currency records. But for some other unknown reason the vast majority of the records do not join, which I can't quite understand and which was the original reason why I didn't think the join on those variables would work and wrote this post. I guess I have to keep playing around to figure out what is going wrong...

[DataNath]

No problem @DMaddy. Joins take place across identical values in the 2 fields you select. Therefore, if you're getting some records that don't find a match/join, then that means that the date/currency pair doesn't exist in one side of the join and you may need to go back to your data and fix this. If you want to include all data even if it doesn't find a match, you can Union the results of the join output anchors to each other in order to recreate what would be a Left, Right or Full Outer Join in SQL etc. Hope this helps!

[DMaddy · Accepted solution]

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.