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?
@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 ,
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
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!
@renjith_nair is missing a by clause
| stats delim="," list(INTEL) as INTEL,list(WEIGHT) as WEIGHT by ID
| nomv INTEL
| nomv WEIGHT