Currency match without knowing the currency

[clio]

Hi everyone,

I have used Alteryx for a while and I want to use Alteryx to do the currency match. 

Here is the case:

I want to find pair transaction in different currency but without knowing the currency and it may occur in same or next day.

I have an exchange rate sheet for reference and the acceptable exchange rate tolerance is +-2%.

I want to find out the match and flag it with unique id for identification.

Example:

Date	Description	Currency	Withdrawal	Deposit
2018-Feb-03	Buy 133	HKD		12100
2018-Feb-04	Sell 122	AUD	2000
2018-Feb-04	Sell 211	HKD	23500
2018-Feb-04	Buy 244	USD		3000
2018-Feb-04	Buy 344	CAD		3000
2018-Feb-04	Sell 900	HKD	18250

And I have a reference sheet like this with base currency in HKD:

Currency	Rate
HKD	1
USD	7.8
CAD	6.1
AUD	6.1

Desired Result:

Date	Description	Currency	Withdrawal	Deposit	Match ID
2018-Feb-03	Buy 133	HKD		12100	1
2018-Feb-04	Sell 122	AUD	2000		1
2018-Feb-04	Sell 211	HKD	23500		2
2018-Feb-04	Buy 244	USD		3000	2
2018-Feb-04	Buy 344	CAD		3000	3
2018-Feb-04	Sell 900	HKD	18250		3

Thanks so much for the help!

[BenMoss · Accepted solution]

Here's how I would solve the problem using the generate rows tool to create 'buffers' and then perform a join.

I have applied the logic to do +-2% but have also applied a logic so it only looks for sell transactions that are within 4 days of a buy transaction.

See attached which gives me exactly the result you were looking for!

Ben

[clio]

Thanks a lot!!