Querying table in database based on specific parameters

Issue Description: I have a flow where I am retrieving data (sales) from several ERP(JD Edwards) tables, and then aggregating the Date fields, as to create one single column containing the date.

Then, I want to retrieve from an exchange rate table, the corresponding rates (monthly) based on the date in the unified column. The principle how this should be retrieved is, for each order date, search in the ER table, and get the exch. rate from the day closest (in descending order) to the order date.

The problem I have is that I do not know which component to use in Alteryx to pass the consolidated field to my query (screenshots attached).

I would be grateful if someone has some suggestions based on a similar scenario!

Workflow.jpg

date_column.jpg

In Database

Database Connection

In-Database

Accepted answers

danrh

Try out the attached. I join on the two exchange rates first to get every possible combination, then add a filter to only take those where CXEFT<= FOM. At this point I order by CXEFT descending so the largest CXEFT is on top (because we've already filtered down to just records where CXEFT <= FOM, the top record at this point will be the one that is closest to FOM). A Unique tool at the end grabs the first record for each unique identifier (I added an ID field in here, you maybe have an order id number? something that makes a record unique from the rest of the records).

Let me know if that still isn't working for your situation.

solution.yxmd

All comments

danrh

The easiest way to do this is likely to bring the entire ER table into your workflow through an Input Data tool and join the two datasets together in Alteryx. In order to get the exch. rate from the closest day, before you join the data you could use the Generate Rows tool to fill in the gaps so every date is contained in your ER dataset - you can read more about the tool here.

Hopefully that works with for you. If you need some more help, post some sample data.

Alex_A

Thank you for your reply! I experimented the last couple of days trying to find a solution but so far I did not manage to make it work as I want to.

Basically I want that for any matching records it retrieves the exchange rate, but also show the rest of the records which are not matching (so those that do not need to be converted). So the problem for me is not the date, but rather the fact that I also have records which do not need conversion. In the "final extrtact" I want to have both records which need to be converted - with the proper exchange rate, and records which do not need to be converted.

I tried to use the Join tool but it's not retrieving all results - either only matches, or only records which do not match.

ER_query.jpg

SO_Query.jpg

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335