Take latest unique values from multivalue field and the corresponding values from another multivalue field?

[TalNiv]

Hi, suppose I have a multi-value field which represents names, which can have different values in each event. for example:

names (ordered by time desc):
event 1: Emma, Dan, Mike
event 2: Dan, Patrick
event 3: Mike, Olivia

In addition, I have another multi-value field which represent the correspond people's grades (correspond by order):

grades (ordered by time desc):
event 1: 80, 70, 100
event 2: 90, 75
event 3: 88, 95

I would like to take for each person his last grade (i.e take all the ever seen people without duplications). My result should look like:

Emma 80
Dan 70
Mike 100
Patrick 75
Olivia 95

[TalNiv]

Thanks for your answer. Although this is a working solution, I wondering if there is another one, because I have a lot of events and they are very big, so mvexpand results pass the 500MB limitation. is there a solution without mvexpand?

[ITWhisperer]

I created a post a while ago about ways to avoid using mvexpand

Help with mvexpand limits, one issue is the memory... - Splunk Community

This may not help if you are actually hitting a memory limit (in which case, nothing helps!)

Having said that, have you considered breaking the search up into smaller chunks (limited to 50,000 events of course) and processing the chunks each with their own mvexpand, then finding the latest for each name from the combined set?

[ITWhisperer]

| eval namegrade = mvzip(names, grades)
| mvexpand namegrade
| eval name = mvindex(split(namegrade,","),0)
| eval grade = mvindex(split(namegrade,","),1)
| stats first(grade) as grade by name