How can do a lookup in diff file?

Hi All

i got a question regarding lookup,

here is the case, i got 1 file with a set of data, with date, currency and amount

and then another file with a set of FX rate with months

what i need to do is, in the Data file add a new column and then Lookup the relevant month (according to the date column, each month will use the relevant month FX in the table) FX value

e.g. if the line with date = 02/08/2021 and Currency = AUD, i need the return value to be 5.5253

May i know how i can do it? thanks in advance

Datasets

Accepted answers

atcodedog05

Hi @Keith_KC_Chan

Here is how you can do it.

Workflow:

Amount:

1. Using formula tool to find first of month so 2020-08-02 is converted to 2020-08-01.

2. Using formula tool to create column key which combination of current and first of month.

Fx Table

1. Using transpose to convert all month columns to rows.

2. Using formula tool to create column key which combination of current and first of month.

3. Using find & replace tool to do a vlookup using key to find values. Find and replace works similar to excel vlookup.

https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201

4.Use select tool to keep only required columns.

Hope this helps 🙂

currency.yxmd

KarolinaRoza

hi @Keith_KC_Chan

Ohh sorry, my mistake, my first solution was not correct. I realized now about that.

I joined data based on exact date (but in FX file you have only rates for 1st day of month, so for example 2020-07-20 was not matched with 2020-07-01).

I corrected my solution and to join, now I am taking only mm-yyyy from each date.

Now for each date from Data file you have assigned fx value.

Let me know if it is fine,

Karolina

data fx.yxzp

All comments

atcodedog05

Hi @Keith_KC_Chan

Can you please share the excel files. So that we can help you out. Its hard to built workflows without data.

Keith_KC_Chan

Hi @atcodedog05

please find the attached data sets, thanks.

Data.xlsx

FX table.xlsx

KarolinaRoza

hi @Keith_KC_Chan ,

you can achieve that in Alteryx by transposing fx table and then use Join tool to populate values.

Hope it is helpful.

Karolina

data fx.yxzp

atcodedog05

Hi @Keith_KC_Chan

Here is how you can do it.

Workflow:

Amount:

1. Using formula tool to find first of month so 2020-08-02 is converted to 2020-08-01.

2. Using formula tool to create column key which combination of current and first of month.

Fx Table

1. Using transpose to convert all month columns to rows.

2. Using formula tool to create column key which combination of current and first of month.

3. Using find & replace tool to do a vlookup using key to find values. Find and replace works similar to excel vlookup.

https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201

4.Use select tool to keep only required columns.

Hope this helps 🙂

currency.yxmd

Keith_KC_Chan

thanks KarolinaRoza

it works, but it seems that the date in the Data file is updated as per the FX file, is there a way for me to keep the date in the Data file? thanks.

KarolinaRoza

hi @Keith_KC_Chan

Ohh sorry, my mistake, my first solution was not correct. I realized now about that.

I joined data based on exact date (but in FX file you have only rates for 1st day of month, so for example 2020-07-20 was not matched with 2020-07-01).

I corrected my solution and to join, now I am taking only mm-yyyy from each date.

Now for each date from Data file you have assigned fx value.

Let me know if it is fine,

Karolina

data fx.yxzp

Keith_KC_Chan

thanks both

problem solved

atcodedog05

Happy to help 🙂 @Keith_KC_Chan

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703