VlookUp and Data Multiply and Divide In Alteryx

[SH_94]

Hi,

Currently, i would like to perform the following by combining data A and data B together.Refer to screenshot below.

1. Vlookup the currency and exchange rate from data A and will give the output as shown in the column M and N

2. Multiply the amount (column L ) with exchange rate (column N) to get the total amount (column O)

3. Divide the final amount (column O) by denotation (column G) to reach the presented amount (column P)

Would like to ask how can I build the workflow above and appreciate if could explain in detail as I still quite new in this Alteryx

[apathetichell · Accepted solution]

I believe this workflow is basically what you are looking for.  What it does is join two databases based upon the company code. It then uses a formula tool (with two formulas) to derive the final amount and presented amount based upon your formulas.

NOTE: The Ghana information is off based upon your data and final goal.

[AngelosPachis · Accepted solution]

Hi @SH_94 ,

Lookups in Alteryx can be done with a couple of tools. The first one is called a join tool, a tool that will allow you to bring together two data streams based on a common field.

In both Data A and B, you have the field [Company] that is common so this is the one you should use to bring the information together. Connecting the Data A to the Left Input anchor and Data B to the right input anchor, you have to define the specific field you need to join on.

Then Alteryx is going to look at the company value in the first record in L input anchor (1111) and look for a match in the right input anchor. If a match is found, then those two data streams will be outputted side-by-side out of the J output anchor. The process goes on  until all matches are found.

When there are no matches to be found, the rest of the records won't disappear but they will fall out of either the L or R output anchors, depending on their input source. So in my example, I have some records falling out of the L output anchor, and the reason for this is that I don't have companies (1115,1116 and 1126) in the Data B table so those don't have a match.

Finally, you just need a formula tool to create your new columns.

There is another way to do lookups, with a Find & Replace tool but let's stick to the Join tool for now. Let me know if you have any questions on the above.

Cheers,

Angelos

[SH_94]

Hi AngelosPachis,

Thank you for your detail explanations. 

May i know is it possible if i didn't select " Denotation field " column during the join tools process as shown in the screenshot below?  And when i create formula , I will extract the information from the original data.

Meaning after the join tool, no denotation field and i use the formula to divide the output with the original data.

Is it workable or it is double work?

[AngelosPachis]

@SH_94not sure what you mean by that, the denotation field that you see after the join is from the original data (from data stream A).

Can you give me an example of what you are looking to do? Should be doable I'm sure