Querying table in database based on specific parameters

[Alex_A]

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!

[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.

[danrh]

If I understand correctly, in SQL terms you need a Left Join?  In Alteryx, you accomplish this by adding a Union tool after your Join.  Something like:

The records that match come out of the "J" stream, and the records that don't find a match come out of the "L" stream.  Union these two together to get every record from your first dataset (your base data) and all the matching records from your second dataset (your exchange rates).  See the attached to see if it clears this up.  If I'm off-base, post some sample data and an example of how it should look after the join.

[Alex_A]

Thanks Dan,

I tried using the Union tool but the records are not joined as I need them to. I was expecting more filtering capabilities. 

I attached the Exchange Rate example and Order Example. Basically for each record in the Order Table I want to retrieve the corresponding exchange rate.

This should be retrieved in the following way: If CXEFT<= FOM And CXCRCD=Currency AND CXCRDC=To Currency  Then get the records with the closest date to FOM and only that record.

Hopefully this clarifies it a bit.

[danrh · Accepted solution]

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.

[Alex_A]

Hi Dan,

Thanks for the help so far! I am still "experimenting" on a smaller identical dataset and seems to be working as expected. I will double check tomorrow, and get back to you!