Reporting

Treating comma separated values as numbers in Pivot data model attribute definition

gregbujak
Path Finder

This question is specific to the new Pivot functionality in Splunk 6.

I have built up my data model, defined all the attributes and am ready to crunch my data.
However, I have a field/attribute (notional) that is a numeric field with comma formatting (ex. 1,000,000).

What is the process for eliminating the comma so that I can properly define it as a number?

I understand how to do this in regular search but cant figure out the secret sauce for pivot.

Thanks

0 Karma

Simon_Fishel
Splunk Employee
Splunk Employee

In the data model editor, you should be able to define an Eval attribute and do whatever you would do in regular search.

gregbujak
Path Finder

You are correct in a very general way.
Following:
http://answers.splunk.com/answers/36792/how-to-sum-numbers-with-commas
Using rex: "(?.)" | convert rmcomma(stripped_notional)
The result was empty.
Using rex: setting field to notional
"(?.
)"| rex field=stripped_notional mode=sed "s/,//g"
Caused a parse error in "//g"

Using eval: setting field to stripped_notional and then the eval expression: replace(notional, ",", "")
finally worked.

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...