How to divide field values into groups depending on a different field?

[nikita012]

I have 2 fields.

**Store Minutes**
81145 22
81234 31
81145 38
87654 35
81234 22
81145 10
87654 13

The data should be divided in two buckets. The first bucket contains number of times when Minutes was less than 20. The other bucket contains number of times when Minutes was between 20 and 40 as shown below.

I want the data to be divided in the below format.

Store Bucket1 Bucket2
81145 1 2
81234 0  2
87654 1  1

Can you please provide code for the same.

[adonio]

here is another version using the bin command
it can scale if for example you have values above 40

  | makeresults 
    | eval _raw="Store Minutes
     81145 22
     81234 31
     81145 38
     87654 35
     81234 22
     81145 10
     87654 13
     81145 22
     81234 11
     81145 28
     87654 38
     81234 72
     81145 70
     87654 53" 
    | multikv 
    | table Store Minutes 
    | bin Minutes span=20 as bucket
    | chart count over Store by bucket

hope it helps

[VatsalJagani]

Hello @nikita012,

Try this query:

| stats list(Minutes) as Minutes by Store
| eval bucket1=mvfilter(Minutes<=20)
| eval bucket2=mvfilter(Minutes>20 AND Minutes<40)
| eval bucket1=mvcount(bucket1)
| eval bucket2=mvcount(bucket2)
| fillnull bucket1,bucket2 value=0
| table Store, bucket1, bucket2

[renjith_nair]

@nikita012 ,

Try

your base search |stats count(eval(Minutes<20)) as Bucket1,count(eval(Minutes>20)) as Bucket2 by Store

[kamlesh_vaghela]

@nikita012

Can you please try this?

YOUR_SEARCH | eval Bucket=case(Minutes<20,"Bucket1",Minutes<40,"Bucket2") | chart count over Store by Bucket

Sample search:

| makeresults | eval _raw="Store Minutes
81145 22
81234 31
81145 38
87654 35
81234 22
81145 10
87654 13" | multikv | table Store Minutes | eval Bucket=case(Minutes<20,"Bucket1",Minutes<40,"Bucket2") | chart count over Store by Bucket

Thanks