Beast Mode for Numeric & Alpha Fields for PayRate Conversion - BambooHR Connector

Tatyana_K
Tatyana_K Member
in Magic ETL

One of our SQL query guru's was able to pull this one together as a beast mode rather than getting complex in the ethers with a magic ETL or MySQL transform.  Below is taking a PayRate value where sometimes there's a 3 letter alpha field for the currency which made the field text. We needed to break out the letters to make the field a value and then multiple those values, depending on the alpha letters, to apply the appropriate conversion. See below. The 999 basically just calls to say it's an alpha value since it will come after 9 in the field. This only seems to be able to work if you have a consistent number of alpha values at the end, which in this case is 3.  The 1 multiplier takes into account that any values that do not have the alpha letters are  USD and will return the value *1 which is all we needed here. Hope this helps someone else out because it took me hours to search and managed to get this (and still waiting for Domo Support to get back to me with reading materials so I can learn the more complex ways of doing this!!)

 

       (0+ case when right(`payRate`,3)> '999' then left(`payRate`,LENGTH(`payrate`)-3)

             else `payRate`

       end)

       *

       case right(`payrate`,3)

             when 'JPY' then 0.0095

             when 'GBP' then 1.4000

             when 'EUR' then 1.2000

             when 'HKD' then 0.1290

             when 'INR' then 0.0160

             when 'CAD' then 0.8000

         else 1

end

Best Answer

  • Tatyana_K
    Tatyana_K Member
    Answer ✓

    I just got clarification from DomoSupport and there is indeed a connector called Open Exchange Rates and there's also an Open Exchange Rates Advanced. I just connected it and I'm going to see what I can do to create a fusion and then use that for my beast mode input! For SalesForce, we also have that connected but we are also using a static conversion calculation for the exchange rate. Ideally we'd want to avoid having to revisit or go back and do anything with that data so I think we may want to see if we can incorporate the Open Exchange Rate with SFDC as well. If you know of any other way that SF tracks that, please let me know - that would be really helpful! Thanks for your input on this and prompting me to look a tad further into the details.

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I would just caution that you will need to update this beastmode any time that you want current conversion rates.  If you take a similar approach from inside a SQL data flow, you could connect it to a datasource that provides updated conversion rates.  

    You would just need to pull out the currency codes.

    SELECT *

    ,CASE

    WHEN RIGHT(`payRate`,3)>'999' THEN RIGHT(`payRate`,3) ELSE 'USD' END

    AS `currencyCode`

    ,LEFT(`payRate`, LENGTH(`payRate`)-3) AS `payAmount`

     

    You could then join this to a table with updated currency codes and conversion rates to get the converted pay rate


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Tatyana_K
    Tatyana_K Member

    That's a great point and I appreciate your input on that. Do you know where I can find a connector that provides the exchange rates so I can get this set up? Thanks!

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I'm not sure if there is a Domo connector or dataset already set up to do this.  However, we use Salesforce.  We want to keep a constant exchange rate for the entire calendar year, so we simply input the data into Salesforce at the start of the year and then extract our conversion rates into Domo.  I suppose for this purpose you could simply create a Domo webform with the currency codes and the conversion rates you are wanting to use.  Then just update the webform as needed.  

     

    Maybe someone else has more experience working with a more constantly changing exchange rate and can point you in the direction of a good connector or dataset to use?

     

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Tatyana_K
    Tatyana_K Member
    Answer ✓

    I just got clarification from DomoSupport and there is indeed a connector called Open Exchange Rates and there's also an Open Exchange Rates Advanced. I just connected it and I'm going to see what I can do to create a fusion and then use that for my beast mode input! For SalesForce, we also have that connected but we are also using a static conversion calculation for the exchange rate. Ideally we'd want to avoid having to revisit or go back and do anything with that data so I think we may want to see if we can incorporate the Open Exchange Rate with SFDC as well. If you know of any other way that SF tracks that, please let me know - that would be really helpful! Thanks for your input on this and prompting me to look a tad further into the details.

Categories