A Conditional Lookup between two sources?

Rizwan

Hi,

I have two data sources One is transaction data and another is Currency Exchange data.

Currency exchange data has a date from which a particular exchange is effective and the rate is effective for a month.

The logic I need is "If the transaction date of a transaction is before the effective date for that month then take previous month value else to take the current month value"

I have tried joining the data based MM-YYYY but it can only help in current month's value, how I can also pick previous month value is something I'm not able to do.

Any help would be appreciated.

I have attached the two sample input sources in the workflow.

Look Up sample data.yxmd

danilang

Hi @Rizwan 

If you look at you input data, you'll see that your initial idea about each exchange rate being effective for a month will give you invalid results.

The exchange rate shown changes almost daily, so you have to match on the date as opposed to the month.

The top branch has your transactions and I converted the transaction date from a DateTime to a Date to join it the exchange data.  In the bottom branch, I sort by From,To and Date, and then trim the trailing spaces from the To Currency field.  Then build an effective range by copying the Date from the next row and using it as the end date for the range.  If there is no end date, then use Effective Date+30 days.  The generate rows tool generates all the date for each possible exchange Rate.  This is used in the two Join tools.  One of them joins on Date and From, the other on Date and To.(Good question @AbhilashR ).  After applying the formula in both cases, you get your results.

Note that I took your initial text inputs and wrote them to .yxdb Alteryx Database files because it took so long to load the configuration info from the massive Exchange Text Input. 

Dan  

CurrencyConversion.yxzp