Foreign Currency Conversion Help

RichYute

I have a dataset that has amount coming in as "Euro", which I would like to convert to "USD". Does anyone know how to go about this in magic ETL?

Preferable I would like to use my own fx rates which as you know is likely to change per month. Is these case statement or other that I can use to get this done please?

RichYute

@GrantSmith This sounds like a workable solution. I will give it a go and comment back on the result.

Thanks,

Rich

GrantSmith

Hi @RichYute

You can use a formula tile to multiple your Euros by your conversion rate to get USD. I'd recommend having a lookup table with your conversion rates in it so it's easier to update where you can join your currency and filter to what you're converting to. I'm not certain there's a specific Domo Connector which would allow you to get this information but the bare bones manual version would be to have a WebForm you go in and edit. There's several APIs out there which will give you realtime updates to currency rates but those typically cost a fee. You'd have to do more investigations into the APIs and if you can use an existing connector to get that information.

amehdad

Hi @RichYute, there's a connector called Open Exchange Rates that you can use. Have a read of this [https://domohelp.domo.com/hc/en-us/articles/360043432893-Open-Exchange-Rates-Connector] before setting it up it in your ETL.

RichYute

Hi Guys,

I wasn't expecting such a quick response from the community, so first let me say thanks. Clearly I am not all that knowledgeable as I would like to think either.

Maybe a question for @GrantSmith ...I understand that Formula tile could be used, but this in my opinion won't help to address the monthly changes in fx rate.

I don't particularly understand how to implement a lookup table, but filling this manually would be doable for my purpose...Are you able to provide more help around this please?

@amehdad I did have a look at the Open exchange connector, but again I was stuck because I am not sure how to used the return value in that connector to then make a calculation on my dataset, so again any further insight will help and please note I will need to do this in Magic ETL somehow.

GrantSmith

@RichYute

You can use the Open Exchange Connector to automatically pull the conversion rates so you won't have to make any manual changes. You can join your currency rates dataset with your dataset amount and then use the formula tile to multiple the Eruos by the conversion rate. It'll look something like this:

I'm adding a constant join column to join the two datasets together. You can Copy and Paste the following code into the New Magic ETL canvas and it'll automatically add the tiles in for you. You'll just need to make sure to change your column names and attach it to the correct input and output datasets.

{"contentType":"domo/dataflow-actions","data":[{"name":"Get USD Conversion Rate","id":"54ba8443-b351-4fa6-a8d5-ce056860045d","type":"Filter","gui":{"x":396,"y":108},"dependsOn":["ede66749-4d14-4537-b11e-bf546cda049d"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Currency Symbol","rightValue":{"value":"USD","type":"STRING"},"operator":"EQ","andFilterList":[]}]},{"name":"Add Join Column","id":"92f3b44b-8eb8-4896-9747-a57859c84d5e","type":"Constant","gui":{"x":516,"y":108},"dependsOn":["54ba8443-b351-4fa6-a8d5-ce056860045d"],"removeByDefault":false,"notes":[],"fields":[{"name":"Join Column","type":"LONG","value":"1"}]},{"name":"Add Join Column 1","id":"5a2803bf-5b0c-40b5-a8dc-84c4606429f5","type":"Constant","gui":{"x":516,"y":252},"dependsOn":["a0fad802-6a29-4eaf-88d2-bb7ad51f62cd"],"removeByDefault":false,"notes":[],"fields":[{"name":"Join Column","type":"LONG","value":"1"}]},{"name":"Join Data","id":"c781e4dc-9bee-4638-a5a1-d0284b6e95d7","type":"MergeJoin","gui":{"x":660,"y":168},"dependsOn":["92f3b44b-8eb8-4896-9747-a57859c84d5e","5a2803bf-5b0c-40b5-a8dc-84c4606429f5"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"92f3b44b-8eb8-4896-9747-a57859c84d5e","step2":"5a2803bf-5b0c-40b5-a8dc-84c4606429f5","keys1":["Join Column"],"keys2":["Join Column"],"schemaModification1":[{"name":"Join Column","rename":"","remove":true}],"schemaModification2":[{"name":"Join Column","rename":"","remove":true}],"partitioningInputId":""},{"name":"Add Formula","id":"ee434bbb-28a5-4031-b034-d67b4a5acd58","type":"ExpressionEvaluator","gui":{"x":756,"y":168},"dependsOn":["c781e4dc-9bee-4638-a5a1-d0284b6e95d7"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"USD Cost","expression":"`Sales` * `Currency Value`","settings":null}]}]}

RichYute

@GrantSmith This sounds like a workable solution. I will give it a go and comment back on the result.

Thanks,

Rich

RichYute

Sorry for my extremely late reply on this, but the solution worked!.