How to merge multiple lookup lines into one

[mhale1982]

I have a table with formatted something like this:

1 John, Smith, a123, superuser, blah
2 John, Smith, a123, audit user, blah
3 Sally, Smith, a234, regular user, blah
4 Andy, Smith, a345, audit user, blah
5 Andy, Smith, a345, log user, blah
6 Andy, Smith, a345, super user, blah

When you run the lookup for the user id (so like a123), you get both results on two lines within the same box in the table.

I want one single line that has the user type concatenated.

So instead of:
a123, super user
a123, audit user

I want:
a123, "super user, audit user"

Is that possible?

[mhale1982]

Anmolpatel got me what I needed specifically (since I was using a lookup table), but the other two are worthy of points and I'll mark them. Thank you all for the great answers!

[manjunathmeti]

hi @mhale1982,

If you need preserve other fields and to join values of other fields with different values in the lookup then try this:

| makeresults 
| eval _raw="first,last,user,role,blah_field
 John, Smith, a123, superuser, blah1
 John, Smith, a123, audit user, blah2
 Sally, Smith, a234, regular user, blah3
 Andy, Smith, a345, audit user, blah4
 Andy, Smith, a345, log user, blah5
 Andy, Smith, a345, super user, blah6" 
| multikv forceheader=1 
| fields - _raw, _time, linecount 
| stats delim="," values(role) as role, values(blah_field) as blah_field by user, first, last 
| nomv role 
| nomv blah_field

[to4kawa]

|makeresults
|eval _raw="No,First,Last,user,role,foo
1,John, Smith, a123, superuser, blah
2,John, Smith, a123, audit user, blah
3,Sally, Smith, a234, regular user, blah
4,Andy, Smith, a345, audit user, blah
5,Andy, Smith, a345, log user, blah
6,Andy, Smith, a345, super user, blah"
|multikv forceheader=1
| table No,First,Last,user,role,foo
| stats values(role) as role by user delim=","
| nomv role

try nomv or |eval role=mvjoin(role,",")
Both commands change multivalue to single.

[anmolpatel · Accepted solution]

This:

| inputlookup test_multiValue.csv
| stats values(*) as * by Column3
| makemv delim="\n " Column4
| rex mode=sed field=Column4 "s/ /, /g"