How do I group two fields (multivalued)?

[philippbloch]

Hello,

I have the following table:

User           Group
-------------  -------------
User_A         Group_A
-------------  -------------
User_A         Group_B
-------------  -------------
User_A         Group_C
-------------  -------------
User_B         Group_A
-------------  -------------
User_B         Group_B
-------------  -------------
User_B         Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D
-------------  -------------
User_D         Group_E

Which I want to group as follows:

User           Group
-------------  -------------
User_A         Group_A
User_B         Group_B
               Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D
               Group_E

I tried:

<my results> | stats values(Group) as Group by User | stats values(User) as User by Group

Any suggestions how to achieve this?

Thanks,
-Philipp

[FrankVl · Accepted solution]

Your attempt is close, but you need to apply a little trick to let the second stats work (as the "by Group" won't have the desired effect when Group is a multi valued field). So you first need turn Group into a single valued field, then do the second stats and then split Group out again. Since values() returns a sorted result, this should work and result in the same concatenated strings for users that have the same set of groups.

<my results>
| stats values(Group) as Group by User
| eval Group=mvjoin(Group,"|")
| stats values(User) as User by Group
| eval Group=split(Group,"|")

This has the following intermediate results:

After the first stats and eval:

 User           Group
 -------------  -------------
 User_A         Group_A|Group_B|Group_C
  -------------  -------------
 User_B         Group_A|Group_B|Group_C
 -------------  -------------
 User_C         Group_C
 -------------  -------------
 User_D         Group_D|Group_E

After the second stats:

 User           Group
 -------------  -------------
 User_A         Group_A|Group_B|Group_C
 User_B         
 -------------  -------------
 User_C         Group_C
 -------------  -------------
 User_D         Group_D|Group_E

After the second eval:

 User           Group
 -------------  -------------
 User_A         Group_A
 User_B         Group_B
                Group_C
 -------------  -------------
 User_C         Group_C
 -------------  -------------
 User_D         Group_D
                Group_E

[philippbloch]

I changed the "mvsplit" to "split" in the second eval.

It works perfect that way! Thanks a lot!

[FrankVl]

You're welcome! And indeed, it should be split not mvsplit, my bad. I've corrected that in my answer as well 🙂

[saurabhkharkar]

just | stats values(Group) as Group by User should be enough

[FrankVl]

No it isn't, as that doesn't group the user field into a multi valued field for users that have the same set of groups.