Find nearest value in numeric multivalue field to other numeric field

[sematag]

I have events with a numeric field "Amount" and a field "User". In a KV Store collection I keep the Amount history values for a each User (Amount_Hist). With a lookup I can get the Amount_Hist for a user in a numeric multivalue field.

Given a new event for a user and the the value of Amount, I need to get the nearest value from the Amount_Hist (where Amount_Hist is a multivalue field and Amount a single value field).

I cant use mvexpand to do it because Amount_Hist is very large and mvexpand produce exesive memory usage when is applied for multiple events.

Thanks a lot for any sugerence.

[dtburrows3]

Not sure if you still need this answered but figure I'd give it a shot in case anybody else has a similar problem they need to solve.

I think something like this would do what you are looking for.

<base_search>
    ``` lookup historical values for each user (potentially mv field) ```
    | lookup <lookup_name> user OUTPUT Amount_Hist
    ``` loop through Amount_Hist values and finding the diff of each compared to the users current value, then take the minimum value and assign it to it's own field "Amount_Hist_min_diff" ```
    | eval
        Amount_Hist_min_diff=min(
            case(
                mvcount(Amount_Hist)==1, abs('Amount_Hist'-'Amount'),
                mvcount(Amount_Hist)>1, mvmap(Amount_Hist, abs('Amount_Hist'-'Amount'))
                )
            ),
        ``` loop back through historical values and only return the values whos diff is equal to the minimum diff value assigned previously ```
        Closest_Amount_Hist_value=mvdedup(
            case(
                mvcount(Amount_Hist)==1, if(abs('Amount_Hist'-'Amount')=='Amount_Hist_min_diff', 'Amount_Hist', null()),
                mvcount(Amount_Hist)>1, mvmap(Amount_Hist, if(abs('Amount_Hist'-'Amount')=='Amount_Hist_min_diff', 'Amount_Hist', null()))
                )
            )

 You can see that the field "Closest_Amount_Hist_value" holds the value closest the the user's current value, this can potentially hold multiple values if they are are equidistant from the current value. As show below.

[bowesmana]

An alternative which will just return a single 'closest' value using foreach is

your_search_and_lookup
| eval diff=max(Amount_Hist)
| foreach Amount_Hist mode=multivalue [ eval new_diff=min(diff, abs(Amount-<<ITEM>>)), closest=if(new_diff<diff, <<ITEM>>, closest), diff=new_diff ]
| fields - new_diff