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
