Use a formula which refers to a different data set

[Yp23]

Hi

New to Alteryx and needed some help!

I wanted to create an alteryx workflow that adds an additional column in data set 2. This additional column should look at the currency in data set 2 (payment currency code), then multiplies the payment amount in data set 2 by the corresponding currency in data set 1.

For example for the first payment amount in data set 2, I want it to multiply 234.8 by 3.7932289163.

Current Workflow

Data Set 1

Data Set 2

What I would like to have at the end (The 5th column at the end with the ILS amount (234.8 by 3.7932289163)

[BS_THE_ANALYST · Accepted solution]

@Yp23 drag a join tool onto the canvas, this will allow you to connect both data streams. The left and right drop down boxes (in the tool's configuration window) are the way you are going to combine them, almost like a VLOOKUP. So select the columns that are going to match up. See pic below:

Just for your understanding, my data before the join:

My data after the join:

Notice the extra column. By the way, I deselected all the other columns coming through (you can see that in the pictures above). After this, you'd just need to use the Formula tool like so:

I've attached the workflow for you to download and use aswell!

Any questions, tag me with the @ symbol
BS

[Yp23]

Amazing thanks!

I have one problem,  indata set 1, I wanted to add one row (it's not in the source data but I will always wanted it to be added and the figure will be the same)

How would I go about such?

I wanted to add in to data set 1:

ILS to ILS with a monthly average of 1.

[Yp23]

All sorted! 

I created a new excel spreadsheet with the same columns with the additional rate and used the union tool to bring them together.