How do I divide data in a column based on their values?

[nikita012]

I have two fields in my data. Below is an example.The actual data contains 100 rows.

Store Minutes
81145 33
81234 42
87654 120
87699 400

I want the data in the below manner.

|||| shows separation of columns. 

40>Minutes>30 ||||||||500>Minutes>100
81145,81234||||||||||||87654,87699

Can you please provide the code for the same?

[knielsen · Accepted solution]

Hi,

Someone will probably come up with a rangemap based solution. I played around with something else here, you could try

<your base search> | streamstats values(eval(if(Minutes>100 AND Minutes<500, Store, null()))) as a, values(eval(if(Minutes>30 AND Minutes<40, Store, null()))) as b | tail 1 | eval a=mvjoin(a,","), b=mvjoin(b,",") | table a b | rename a as "40>Minutes>30", b as "500>Minutes>100"

Adjust the parameters to your need and add as many more columns as needed.

To test this out, I used your example data (changing 42 to 32 so it actually falls into the first category) in a run-everywhere snippet:

| makeresults | eval input="81135,33%81234,32%87654,120%87699,400" | makemv delim="%" input | mvexpand input | rex field=input "(?<Store>\d+),(?<Minutes>\d+)" | streamstats values(eval(if(Minutes>100 AND Minutes<500, Store, null()))) as a, values(eval(if(Minutes>30 AND Minutes<40, Store, null()))) as b | tail 1 | eval a=mvjoin(a,","), b=mvjoin(b,",") | table a b | rename a as "40>Minutes>30", b as "500>Minutes>100"

Hth, but wait for someone else to jump in. I am sure there is a more elegant solution. 😉

[nikita012]

Thanks it worked perfectly.

[p_gurav]

try this:

<your search> | eval level=case(minutes<50 AND minutes>30, "50>Minutes>30", minutes<500 AND minutes>100, "500>Minutes>100" ) | chart values(store) as store over level by _time