I have a search which produces a stats
with 2 columns, and n = 3k
, where k is an integer, rows. The second column is a simple integer, and the rows form logical triplets, like so:
foo_x 2
foo_y 2
foo_z 5
bar_x 12
bar_y 5
bar_z 9
...
How can I transform my result into a form with k
rows and 3+1
columns, like so?:
x y z
foo 2 2 5
bar 12 5 9
Normally I would achieve this by changing the form of the query that produces the n
rows into something more sensible. In this case, I'm not sure if that's possible - read on for why -
3k
rowsI have a list of users, and k
properties of those users, like so:
fishs_age dogs_age cats_age babys_age
user_a
user_b
user_c
...
For each property, I classify 3
risk labels (they need to be separate so I can later sum
them):
| foreach fishs_age,
dogs_age,
cats_age [eval <<FIELD>>_hi = if(<<FIELD>> >= 9, 1, 0) ,
<<FIELD>>_md = if(<<FIELD>> >= 4 AND <<FIELD>> < 9, 1, 0) ,
<<FIELD>>_lo = if(<<FIELD>> >= 2 AND <<FIELD>> < 4, 1, 0)]
I then calculate sum
s and flip the axes in hopes to make a chart:
| stats sum(*_lo), sum(*_md), sum(*_hi)
| rename sum(*) as *
| transpose
And this is the source of the original dataset
foo_x 2
foo_y 2
foo_z 5
bar_x 12
bar_y 5
bar_z 9
...
i.e.
fishs_age_hi 2
fishs_age_md 5
fishs_age_lo 6
dogs_age_hi 3
dogs_age_md 4
dogs_age_lo 12
...
Again: what I want is to transform this into:
x y z
foo 2 2 5
bar 12 5 9
i.e.
hi md lo
fishs_age 2 2 5
dogs_age 12 5 9
Assuming this is how your original data showed up in respective fields myField
and value
:
myField value
dogs_age_lo 12
dogs_age_md 4
dogs_age_hi 3
fishs_age_lo 6
fishs_age_md 5
fishs_age_hi 2
Then try this:
your query to return myField and value
| rex field=myField "(?<fieldName>.*)_(?<columnName>[\S]+)"
| chart values(value) over fieldName by columnName
Assuming this is how your original data showed up in respective fields myField
and value
:
myField value
dogs_age_lo 12
dogs_age_md 4
dogs_age_hi 3
fishs_age_lo 6
fishs_age_md 5
fishs_age_hi 2
Then try this:
your query to return myField and value
| rex field=myField "(?<fieldName>.*)_(?<columnName>[\S]+)"
| chart values(value) over fieldName by columnName
That's done it - thanks a bunch! I would have never found this functionality without your help.