Substring of multivalues out ot multivalue field

[geoffmartin]

I'm trying to produce a multivalue field out of another multivalue field in my data model, and that's proven to be quite complicated. This is what I'm trying to achieve:

I have a multivalue field with application names, like:

Application:
   Word
   Excel
   PowerPoint

but since I have too many applications, I created a field submenu, in which you select the application first letter, and that filters the Application field. Then, out of the field above, I want to have one called "Submenu" with the following:

Submenu:
  W
  E
  P

And my tstats command do the rest on grouping and filtering. I tried to use substring but it doesn't work for multivalue fields, only the single-valued results of the field.

Any ideas?

[somesoni2 · Accepted solution]

Try something like this

Your base search with field Application | eval SubMenu=Application | rex mode=sed field=SubMenu "s/(\w)(\w+)/\1/g" 

This will create a field SubMenu which will have same number of elements as Application and will contains just the first character from Application field values.