Eventstats, eval and "category" assignment

[betto86]

Hi all 🙂

I need you help because I can't figure out how to solve this problem.
Suppose we have a table, made of two fields, as the one following:

Name    Value

A        1.1  
A        0.3  
A        1.9  
A        1.2  
A        0.9  
B        0.9  
B        0.9  
B        1.1  
B        1.6  

I need to create a new field (Id) in order to assign to values for each name, a sort of category following this rule:

Values with the same ID are contiguous values in range 0.7 < Value < 1.3. If the value is out of this interval, assign a new Id. When parsing a new Name value, the Id must restart from 0.

That is, I need to obtain this table:

Name    Value    Id

A        1.1     0   
A        0.3     1
A        1.9     2
A        1.2     3
A        0.9     3
B        0.9     0
B        0.9     0
B        1.1     0
B        1.6     1

This is a part of my search:

eval id = 0 |
streamstats current=t global=f window=2 first(id) as precId last(Value) as currValue  by Name |
eval id=if((currValue  > 0.7 AND currValue  < 1.3), precId, precId+1) |
table myTime,  process, destip, _time , absTmp, mediana, rapporto, id, heartId, currRapporto, precId, newId

I'm just parsing each row with streamstats, taking information from the previous row in order to know the correct Id to assign to the parsed row. But it seems not to work.

Any help or suggestion will be appreciated,

thank you guys! 🙂

[martin_mueller]

Consider this:

| stats count | eval foo = "A-1.1,A-0.3,A-1.9,A-1.2,A-0.9,B-0.9,B-0.9,B-1.1,B-1.6" | makemv foo delim="," | mvexpand foo | eval Name = replace(foo, "-.*", "") | eval Value = tonumber(replace(foo, ".*-", "")) | fields - count foo | eval temp = if(0.7 < Value AND Value < 1.3, 0, 1) | delta temp as delta | eval temp = if(delta == -1, 1, temp) | streamstats sum(temp) as Id by Name | fields - temp delta