Replacing open bracket with minus

Richard B.

I've been asked to get data from a plain text report file into excel using Monarch. All is good and the model I've made is doing exactly as requested. The problem is that the report as it stands (generated from another system entirely) presents one column of monetary amounts, and when there are negative values, there is only an open bracket around the value, instead of an open and closed bracket to denote it being a negative.

Obviously the source system needs to fix that, but in Monarch, is there a way I can get it to go down every cell in that column (column name is 'Additions') and if it detects a "(" to replace it with a "-"  ?

I know there is a REPLACE function in calculated fields but I couldn't get this to work with what I'm asking.

Summarising: I've got my report, got my model, applied my model to report, happy with data when I look in Window->Table, except for this one column where there are negative values showing as "(2345.99" instead of "-2345.99"

See the record halfway down the attached report file to see what I'm dealing with.

 

Thanks!

Cost Summary test file.txt

CPorthouse

Are you using Monarch Classic or Data Prep Studio?  Is that column coming in as a character field or numeric?  If it is coming in as a numeric field, I am guessing the column is being converted to a null value and therefore the amount is lost.  If that is the case, make sure the format type is character instead of numeric.

From there, in DPS you can use Replace->Find & Replace to look for ( and replace with - and then change the column to be a numeric data type.

In Monarch Classic, use Table design and create a new formula field val(trim(replace(F4,"(","-")))