Getting Data In
Highlighted

How to remove the currency symbol etc. from a field before indexing?

Legend

This is what the data looks like in the source file (.csv). Notice the $156.03

09/26/13, 2013 , 09-Sep , Week-39 , Thu , -   , 4 ,, $156.03 ,, $156.03 ,100%, $39.01 ,,0:00, 13 , $12.00 ,

This is what I have in my \etc\system\local\props.conf

SEDCMD-makenumeric = s/\$(\d)/\1/g s/(\d)%/\1/g s/\$-//g s/(\d),(\d)/\1\2/g s/\s?,\s?/,/g

This is what the data looks like in raw mode in data preview (no currency symbol). SEDCMD appears to be working.

09/26/13,2013,09-Sep,Week-39,Thu,-  ,4,,156.03,,156.03,100,39.01,,0:00,13,12.00,

However, the $ symbol is back in the List and Table mode in Data Preview

09/26/13,2013,09-Sep,Week-39,Thu,-  ,4,,156.03,,156.03,100,39.01,,0:00,13,12.00,
Avg _ Cust = $39.01 Conv = 100% DOW = Thu Date = 09/26/13 Hrs Open = 13 Hrs Worked = 0:00 In_Store = $156.03 Month = 09-Sep No Sale = - Sale _ Hr = $12.00 Sales = 4 Sales Amt = $156.03 Wk No = Week-39 Year = 2013

If I go through with the data import, this search results in the field with $ symbol

index=* | table "Sales Amt"

Question: How do I get rid of the currency symbol (& other string characters) so a number can be stored as a numeric value when indexed?

0 Karma
Highlighted

Re: How to remove the currency symbol etc. from a field before indexing?

Champion

This is because your SEDCMD is applied to the rawdata, but (assuming you are using standard settings for csv data) your indexed fields are extracted from the rawdata prior to SEMDCMD, so they still contain the original data (with dollar sign). Read some more here where I came across the problem earlier.

You need to change your indexed field as well. If you don't know how to after reading up on the link I posted, feel free to ask further.

View solution in original post