Lookup a reference table file to the main file

Hi Alteryx Community

I have a file that has current exchange rate as so in Table 1

CurrentFxDate	CurrentFxRate (USD)
26-SEP-2019	0.914076813

and another excel file with an amount to convert in Table 2

Amt (EUR)

12000

How do I convert it by add another column (Amt USD) in Table 2 and using the formula tool to reference table 1 data and convert EUR to USD in Table 2

Preview of final Table 2

Amt (EUR)	Amt (USD)
12000	13128.09

Please help me as I tried using Join tool but im unable to get the final output.

Help

Accepted answers

estherb47

If you're joining one row to one row, configure the join tool to join on record position. Then you can add a formula tool to create the calculation.

If Table 2 is one line, and Table 1 has many, then try the Append tool to append Table 2 onto Table 1. Be sure to set the tool to allow all appends.

Let me know if that helps!

Cheers,

Esther

estherb47

Do your numbers actually have the commas in them? That would make them text. You'd need to remove the commas (data cleansing is great for that) and then convert into a number.

All comments

estherb47

If you're joining one row to one row, configure the join tool to join on record position. Then you can add a formula tool to create the calculation.

If Table 2 is one line, and Table 1 has many, then try the Append tool to append Table 2 onto Table 1. Be sure to set the tool to allow all appends.

Let me know if that helps!

Cheers,

Esther

henrygeorge

Thank you for the solution. But i just get first 3 numbers as my output despite choosing my data type as double eg- 208,065,706 is giving an output of 208 and post exchange rate output of 227.551 . I don't know why is this happening as I cannot use the formula tool with a string data type.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703