DIfferent exchange rate applied to the amount

[SH_94]

Hi Community,

I would like to translate those amount value to certain currency as shown in the screenshot below. I plan to use formula tools to apply these exchange rate and presented value without applying the text input tool (to import the exchange rate and presented value).

May i know if anyone know how to build the workflow to cater the need of situation below.

[afv2688]

Hello @SH_94 ,

You can have the amount "translated as you say but it would be managed as a string.

This means, you can have the numbers to be shown either with thousands separators or not, but if you decide to not have them and to be shown only in a specific coulmn this would be then needed to be transformed into a string.

Would this be ok for you?

[AngelosPachis · Accepted solution]

Hi @SH_94 ,

You can use a find and replace tool to lookup the values of currency between the two different datasets and then append the exchange rate and presented value.

Then with a formula tool, you can estimate the translated amount

Cheers,

Angelos

[SH_94]

Hi @afv2688 ,

Yes,it is fine with me but i not sure how to build that up in the alteryx. Do you have any idea on this?

Lastly, can i ask , if it ady transformed into string, can we change it back to number again as i want to do some calculation ( adding or minus ) with other figures.

[SH_94]

Hi @AngelosPachis ,

Can i ask is it possible if we only using the formula tools as shown below to create the formula, without importing Data A.

Lastly, may i know in what situations we can use the Find and Replace tools to help in the alteryx?

[AngelosPachis]

Hi @SH_94 ,

Yes you can do that, but you should have the data you need to process in the format shown below

As you need all information provided in those fields. 

A find & replace tool works best when you are trying to lookup or find some values in one data stream to a field contained in another data stream, as I have done with the two inputs in the example provided.

Hope that helps

Angelos

[SH_94]

Hi @AngelosPachis ,

Thank you for the post previously. I just went through my data and noted i have two criteria that we need to vlookup which is different with previous data (1 criteria) as per screenshot below. Before this, we just need to find and replace the currency and the alteryx will find for us the exchange and presented value in information.

However, currently i need to vlookup country and currency first , then populate the result for the exchange rate and presented value in.

May i know can we still do it using find and replace tool? Could you guide me on how to build the workflow? I also attached the file as shown below. 

[AngelosPachis · Accepted solution]

Hi @SH_94 ,

There are two ways you can go about it now. As you said, the Find & Replace tool will work best for a single condition, and now that you have two conditions the use of it is not ideal.

What you can do instead is use a Join tool, that will allow you to join the two datasets on both Currency and Country fields, and then proceed normally with the formula tool.

An alternative would be to create a new concatenated lookup field consisted out of country and currency as shown below

That can act as a unique identifier between your two streams, so with that new field you have converted the two different conditions for country and currency into a single condition (I named it lookup here). Then you can go on and use the Find & Replace tool by looking up that new field you have created.

I have created both ways, you can try recreating it and use the workflow if you need any hints

Hope that helps,

Angelos

[SH_94]

Hi @AngelosPachis ,

Thanks a lot for the sharing and it really help a lot to determine what tools to be used in this case.

I have one query on the formula tool applied as per screenshot below. May i know it is good to have the formula ending with //as a number when we deal with amount /value? May i know how this formula helps us in certain scenario?

Thank you again for your help.

[AngelosPachis]

Hey @SH_94 ,

Whatever comes after // in a formula expression is treated by alteryx as notes and is not considered part of the expression.

So in that expression box, I have added a note for you so you are aware about the difference between the Translated Amount calculation (which is a number, hence the note "as a number") and the Translated Amount text field.

Let me know if you have any questions on this and whether that's clear, my bad I should have mentioned it in my previous post.

Cheers,

Angelos