How can do a lookup in diff file?

[Keith_KC_Chan]

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

[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.

[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

[atcodedog05 · Accepted solution]

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 🙂

[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 · Accepted solution]

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

[Keith_KC_Chan]

thanks both

problem solved

[atcodedog05]

Happy to help 🙂 @Keith_KC_Chan