Forum Discussion

m_b_b_15's avatar
m_b_b_15
New Contributor III
4 hours ago

Complex expression to remove currency symbols?

Hi all, we have a process to format data in the csv before it's imported into OneStream.  This seems like extra work and I want to create a complex expression in the Data Source to remove this step. My excel formula is =VALUE(SUBSTITUTE(SUBSTITUTE(H9,"¥",""),",","")).... all the way down for column H or the Amt field import.

I've tried putting this in the complex expression but I do not have the dimensions defined properly.... does anyone have an example or suggestions they can point me towards? 

thank you!!!

Data Sources
Transformation

1 Reply

  • sameburn's avatar
    sameburn
    Icon for OneStream Employee rankOneStream Employee
    2 hours ago

    Hi m_b_b_15

    Excel formulas won't work in a complex expression.  However you can use string manipulation to extract what you need.  Here is a simple example using substring that can get you started based on your description.  You can add this to an Extensibility Rule in OneStream and click the Execute Extender button to see results (while you develop e.g. see screenshot below) and then migrate to your complex expression once happy with the results e.g.

    Dim sb As New Text.StringBuilder	

Dim input As String = "$3563.133"
sb.AppendLine($"Original Value ➡ {input}.")

Dim currency As String = input.Substring(0, 1)

sb.AppendLine($"Extracted Currency ➡ {currency}.")

Dim amount As Decimal = Decimal.Parse(input.Substring(1))

sb.AppendLine($"Extracted Amount ➡ {amount}.")
				
Throw New XFException(sb.ToString())

    Hope this helps