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?
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.
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.