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.

[AbhilashR]

@Rizwan - 

If you had to join the two datasets on date and currency, which currency column in your Currency Exchange data would you join them on? Would it be From Currency or To Currency?

One approach would be to join the datasets on currency and then apply filters downstream, hence the question.

[danilang · Accepted solution]

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  

[Rizwan]

Sorry, I did not mention that, I need to use From currency Column.

[Rizwan]

thanks a lot Dan!

I'll try that, this rate exchange is coming from SAP, it is Corporate type Exchange rates which are valid for a fiscal month I believe. I'll try and see if I have a more granular exchange rate than this.

Can you import it to a previous version? I'm using 2018.1 and can't import it 😐

Thanks 🙂

[ChrisTX]

See attached file: Adjusting Alteryx files for different versions

This will work as long as the workflow doesn't use features only available in later versions.

Chris

[danilang]

Hi @Rizwan 

Here's a version adjusted work flow.  Just copy it into the same directory where you extracted the previous package

Dan