Recombining events after mvexpand

[DATT]

Hello everybody,

I'm working on a query that does the following:

1. Pull records, mvexpand on a field named INTEL. This is a multi-value field that could have anywhere from 1 to 11 different values.

2. Once expanded, perform a lookup using INTEL to retrieve a field WEIGHT. A weight is assigned to each INTEL value, between 1 and 5.

3. After the lookup, collapse the split records back into one record. 

At first glance I figured I could do `... | mvexpand | lookup | mvcombine | nomv` but since the records are no longer identical because both INTEL and WEIGHT are different, I don't think I can use mvcombine anymore.

To Visually demonstrate the issue

ID	INTEL
12345	A, B, C, D

After mvexpand

ID	INTEL
12345	A
12345	B
12345	C
12345	D

After Lookup

ID	INTEL	WEIGHT
123456	A	1
123456	B	2
123456	C	3
123456	D	4

Ultimately, I would like to get back to this

ID	INTEL	WEIGHT
123456	A,B,C,D	1,2,3,4

Any tips?

[renjith_nair · Accepted solution]

@DATT ,
try using stats on those values

| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT 
| nomv INTEL
| nomv WEIGHT

Here is a run anywhere example . Add/remove your columns according to the requirements

| makeresults | fields - _time 
| eval INTEL="A B C D E" | makemv INTEL | mvexpand INTEL 
| streamstats count | eval WEIGHT=count | rename count as ID 
| makemv delim="," INTEL 
| rename comment as "Above is just data generation" 
| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT 
| nomv INTEL 
| nomv WEIGHT

[DATT]

This got me close enough to what I needed.  In my effort to streamline and reduce clutter I oversimplified the issue in my original post.  In any case though, thank you for the help!

[ITWhisperer]

@renjith_nair is missing a by clause

| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT by ID
| nomv INTEL
| nomv WEIGHT