This is the data set from Fundamental 1.
A lot of successful purchase events with same 'ProductName' doesn't include 'categoryId' as a field. So I want to put/replace the null 'categoryId' field with 'categoryId' which have the same 'ProductName'. How do I do it and scale it up to multiple events?
For example, I want the Null value in the screenshot above replaced by the categoryId of the same ProductName.
I hope I word this out clearly. Thank you in advance.
I am assuming your example shows the word "Null" as the text value rather than Splunk 'null', so in that case, this is an example where I have shown your data plus a couple of additional games to demonstrate the point.
| makeresults
| eval _raw="ProductName,categoryId
Benign Space Debris,Arcade
Orvil The Wolverine,Arcade
Some Other Game,Board
Another New Game,Null
Orvil The Wolverine,Null
Another New Game,Ball
Benign Space Debris,Null
Some Other Game,Null"
| multikv forceheader=1
| table ProductName categoryId
| rename "COMMENT" as "Above this line is your data setup"
| eval categoryId=nullif(categoryId, "Null")
| sort ProductName, categoryId
| filldown categoryId
This works by ensuring that the text "Null" is converted to a Splunk null and then sorts the data by ProductName and categoryId, which is ensuring that the null entries are at the end of the product name.
It then uses filldown to take a current categoryId and places it in subsequent null records below.
Hope this helps.
I am assuming your example shows the word "Null" as the text value rather than Splunk 'null', so in that case, this is an example where I have shown your data plus a couple of additional games to demonstrate the point.
| makeresults
| eval _raw="ProductName,categoryId
Benign Space Debris,Arcade
Orvil The Wolverine,Arcade
Some Other Game,Board
Another New Game,Null
Orvil The Wolverine,Null
Another New Game,Ball
Benign Space Debris,Null
Some Other Game,Null"
| multikv forceheader=1
| table ProductName categoryId
| rename "COMMENT" as "Above this line is your data setup"
| eval categoryId=nullif(categoryId, "Null")
| sort ProductName, categoryId
| filldown categoryId
This works by ensuring that the text "Null" is converted to a Splunk null and then sorts the data by ProductName and categoryId, which is ensuring that the null entries are at the end of the product name.
It then uses filldown to take a current categoryId and places it in subsequent null records below.
Hope this helps.