Best Of

@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}]}]}

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.

@Emma

Try this:

CASE WHEN `Plant`=1302 AND `Line of Business`='Cable' THEN 'QC Fibre' END

I used the Domo Governance Third Part Connector to pull the Beast Modes report and then utilized a filter, groupby and another filter in magic ETL 2.0 to determine if there were any that occurred more than once. You can click anywhere in your ETL canvas and then use CMD+V or CTRL+V to past in the code I used below to automatically populate the tiles I was using.

{"contentType":"domo/dataflow-actions","data":[{"name":"Not Saved to Dataset","id":"d2c4d139-4bac-410f-ac73-725edb870fa5","type":"Filter","gui":{"x":366,"y":1680,"color":null,"colorSource":null},"dependsOn":["17016d2d-23ff-4d71-8593-b9e599c6f727"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Card ID","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Group By 18","id":"eb2aae43-b929-4ddf-9208-974b483da346","type":"GroupBy","gui":{"x":480,"y":1668,"color":null,"colorSource":null},"dependsOn":["d2c4d139-4bac-410f-ac73-725edb870fa5"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Dataset Name"},{"name":"Beast Mode Name"},{"name":"Beast Mode Formula"}],"partitionedAggregation":false,"fields":[{"name":"Count","source":"Beast Mode ID","type":"COUNT_DISTINCT","valuefield":null}]},{"name":"More than one occurrence","id":"95cccd1e-a9e2-4cbc-8a64-ceb62222ea72","type":"Filter","gui":{"x":576,"y":1656,"color":null,"colorSource":null},"dependsOn":["eb2aae43-b929-4ddf-9208-974b483da346"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Count","rightExpr":null,"rightValue":{"value":"1","type":"DOUBLE"},"operator":"GT","andFilterList":[]}]}]}

You'll need to add your input dataset manually and connect it to the first filter.

https://domohelp.domo.com/hc/en-us/articles/360056318074-Domo-Governance-Datasets-Connector is the KB article about it. It should be just the Domo Governance Connector. It was called the Domo Governance Thirdy Party Connector at one point.

https://[INSTANCE].domo.com/appstore/connectors/com.thirdparty.connector.d14c2fef-49a8-4898-8ddd-f64998005600 should take you to it. just replace [INSTANCE] with your instance name in the URL.