Here is my data in the table:
Index Field1 Field2
1 0 A,B,C
1 -5 D,E,F
1 -10 G,H,I
I have a complex query that returns a single row of data that includes index, field1 (as a multi-value field) and field2 (as a multi-value field) like:
1 0,-5,-10 A,B,C,D,E,F,G,H,I
I can get field 2 to return just one value by utilizing the min command that gives me the desired result. If I use the mvexpand field2 I limit the result to index =1, I get the below result:
1 -10, A,B,C
Looking at the data, I want the row for field2 to be the row that corresponds with the the value of the min statement. For a result of:
1, -10 G,H,I
Hope this makes sense. What I am really looking for is the query to return the value based on the value of another field knowing that I currently get have the result in a multi-value field.
| makeresults
| eval _raw="Index Field1 Field2
1 0 A,B,C
1 -5 D,E,F
1 -10 G,H,I"
| multikv
| stats values(Field1) as Field1 values(Field2) as Field2 by Index
`comment("Up to this point is the creation of dummy data.")`
`comment("Create Table")`
| mvexpand Field2
| mvexpand Field1
| sort - Field1
| streamstats dc(Field1) as count
| eval table_tmp=Field1."#".Field2
| stats values(table_tmp) as table_tmp values(Index) as Index by count
| eval table_data=mvindex(table_tmp,count-1)
| rex field=table_data "(?<Field1>[^#]+)#(?<Field2>[^#]+)"
| fields Index Field1 Field2
Hi, It's strange when it comes from dummy data.