+ sign on my amounts
Brenda Hopkins asked 6 years ago
how can I remove the + sign on my positive amounts in my text file in Smartconnect. I have it setup to look at all files in the folder. But the positive amounts are all blank due to the + sign. I am able to fix the – sign but I do not even see the positive amounts.
Answers
Patrick Roth Staff answered 6 years ago
Well, if your source is a flat csv/tab file, just change the schema. Right now it must be coming in as a “char” field vs decimal. If you switch it to Char by changing the schema in the ‘create schema file’ button then SC/ODBC should read it as decimal and ideally throw out the+ symbol.
But assuming this isn’t csv and we can’t do that, your other option would write a calculation to remove the first char.
so I think something like:
if _MYCOLUMN.Trim().SubString = “+” then
return _MYCOLUMN.Remove(0,1).Trim()
else
return _MYCOLUMN.Trim()
end if
But assuming this isn’t csv and we can’t do that, your other option would write a calculation to remove the first char.
so I think something like:
if _MYCOLUMN.Trim().SubString = “+” then
return _MYCOLUMN.Remove(0,1).Trim()
else
return _MYCOLUMN.Trim()
end if
Brenda replied 6 years ago I can now see the positive amounts by changing to CHAR. But the (+) is still showing so I assume I need to remove that. Tried your statement above but get error: overload resolution failed because no accessible ‘substring’ accepts this number of arguments.
Lorren Zemke replied 6 years ago Brenda, another method is to use _MYCOLUMN.Replace(“+”,””).Trim()
This will replace every instance of the plus sign in the string and doesn’t matter if it has a plus sign or not.