topic Unable to add numbers because of currency sign and commas present in the field in Splunk Search

Unable to add numbers because of currency sign and commas present in the field

varad_joshi — Thu, 16 Feb 2017 08:46:14 GMT

I saw some answers already however did not find anything concrete so asking a new question.

I have a field where values are in USD, like $123,4.00.

I want to run addcoltotals on the field, and bring back the currency sign and commas once the operation is done.

I got rid of currency sign and commas using following:

| replace "$" with "" in | replace "," with ** in

Not sure if I need to use replace twice, using a comma didnt work for me so using replace command twice.

Now which would be the best way to get rid of $ and , signs and add the numbers. And then bring back those signs again.

Re: Unable to add numbers because of currency sign and commas present in the field

niketn — Thu, 16 Feb 2017 10:03:17 GMT

You would need to escape $ with a backslash as shown in example below. If your fields are numeric try to fieldformat for formatting the results for display only (underlying value will not change only display will change.)

Following is a run anywhere example. I have used cascaded replace to remove $ and Comma.

 | makeresults 
 | eval Amount1="$12,000.00"
 | eval Amount2="$12,340.00"
 | eval Amount1=replace(replace(Amount1,"\$",""),",","")
 | eval Amount2=replace(replace(Amount2,"\$",""),",","")
 | eval Total = Amount1 + Amount2
 | eval Total= "$".tostring(Total,"commas")

Re: Unable to add numbers because of currency sign and commas present in the field

varad_joshi — Thu, 16 Feb 2017 12:16:33 GMT

I did this to get rid of $ and commas

| replace "$" with "" in "My Field" | replace "," with ** in "My Field"

And used your last line to add $ and commas again.

| eval "My Field" = "$".tostring('My field',"commas")

Only thing is, many of my values are now blank.

Only the amounts with total 3 digits are being populated. They won't have commas.

Any value above $999 i.e $1,000 is not getting populated.