Lookup a reference table file to the main file

[henrygeorge]

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.

[estherb47 · Accepted solution]

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]

Hi

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.

[estherb47 · Accepted solution]

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.

[henrygeorge]

Thank You so much for the Help!

[henrygeorge]

Hi

But its removing the negative signs prior to the number. I used data cleansing tool and I just ticked the remove punctuation. The conversion from string using data cleansing to remove the commas and then converting it to double does output the whole number but also unfortunately removes the negative sign. Please help.