VLOOKUP/LOOKUP currency symbol

I have a column where

if [Currency] = "U$" THEN [Mkt Value]*1.2727ELSE [Mkt Value] ENDIF

currently I have hardcoded the rate, but my fx rates source file is a separate file that's updated daily.

would it be possible, in the file's current format, to get the rate that's beside "CLOSE" (that's the FX rate I use daily) by doing some sort of lookup without me looking at the file then hardcoding the number?

*****

also, for month-end, i would need to do a similar lookup with not only U$, but other currencies like EM, BP, etc (those rates are in the second sheet, "ME"). i have provided one example in sheet3....would this be doable in Alteryx?

FX Rates Sample.xlsx

Tips and Tricks

Accepted answers

Yoshiro_Fujimori

Hi @daveyc3000 ,

If the position of the cell for exchange rate is fixed, you can simply pick up the row and the column to get the value.

And you can use Join tool to get a corresponding rate from a rate table.

Here is a sample workflow. I hope it helps. Good luck.

Workflow

1268329.yxzp

All comments

Yoshiro_Fujimori

Hi @daveyc3000 ,

If the position of the cell for exchange rate is fixed, you can simply pick up the row and the column to get the value.

And you can use Join tool to get a corresponding rate from a rate table.

Here is a sample workflow. I hope it helps. Good luck.

Workflow

1268329.yxzp

daveyc3000

excellent thank you so much!

daveyc3000

Actually, sorry, for the top workfllow....I'm also trying to integrate that rate to another file (attached) , where the rate is represented by the symbol U$ in the "Currency" column.

the issue I'm encounter when I try to join the file is that after joining the two files, I'm only getting the "U$" results and not the rows with blanks in the currency column.

they are blank because they're our native currency and thus no conversion is required.

sample other file.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703