How to lookup against a csv file and join data with a multi-value field?

[axdahl]

I have a lookup file that is basically the following:

userid,group
1,g1
1,g2
1,g3
2,g3
2,g1

I want to do a lookup against this table and return a multivalue field for each event.

i.e. post lookup, if I do table userid, group, I should see:

userid   group
------------------------
1        g1
         g2
         g3
------------------------
2        g1
         g3
------------------------

Basically the lookup should return all matches as a multivalue field. Right now if I'm using

.... | join max=0 userid [inputlookup testgroup.csv ] | table userId group...

But what happens is that each event just gets a single value (g1, g2 or g3) returned for group instead of a multivalued field that contains all matches.

[strive · Accepted solution]

Try this

.... | join max=0 userid [|inputlookup testgroup.csv ] | stats values(group) as group by userid

[axdahl]

that was it!, thanks.