How to merge the content of a group

[junlozhang]

Let's say the data looks like:

StudentName	StudentId	Grade	ExamDate
Tom	1	60	2021-04-01
Jerry	2	70	2021-04-01
Tom	1	62	2021-04-07
Jerry	2	55	2021-04-07

And the the result I want looks like:

Formatted

Tom,1:2021-04-01,60;2021-04-07,62

Jerry,2;2021-04-01,70;2021-04-07,55

I want to divide the origin data into groups by key "StudentId", and then merge the contents in each group to make a formatted string

Of course, I can get all the data and write a program in Python or Java to process it.... But it would be better if I can do this only with SPL

I have written a script to group by "StudentId":

transaction StudentId
| stats list(_raw) as rawList by StudentId

But don't know what to do next

[kamlesh_vaghela · Accepted solution]

@junlozhang 

Can you please try this? Just modify search as per your requirement. 

YOUR_SEARCH
| stats values(StudentName) as StudentName list(Grade) as Grade list(ExamDate) as ExamDate by StudentId 
| eval ExamDate = mvzip(ExamDate,Grade) | mvexpand ExamDate
| stats values(ExamDate) as ExamDate values(StudentName) as StudentName  by StudentId | eval Formatted=StudentName.",".StudentId.":".mvjoin(ExamDate,";") | fields Formatted

Sample Search:

| makeresults 
| eval _raw="StudentName	StudentId	Grade	ExamDate
Tom	1	60	2021-04-01
Jerry	2	70	2021-04-01
Tom	1	62	2021-04-07
Jerry	2	55	2021-04-07
" 
| multikv forceheader=1 
| stats values(StudentName) as StudentName list(Grade) as Grade list(ExamDate) as ExamDate by StudentId 
| eval ExamDate = mvzip(ExamDate,Grade) | mvexpand ExamDate
| stats values(ExamDate) as ExamDate values(StudentName) as StudentName  by StudentId | eval Formatted=StudentName.",".StudentId.":".mvjoin(ExamDate,";") | fields Formatted

[junlozhang]

Hi, I got another question:

About string concat, 

I want the separator to be white space "\t", and I called the function like

mvjoin(ExamDate, "\t")

and I want a special Unicode character "\u0006" and I called the function like

StudentName."\u0006".StudentId

and I got the literal "\t" and "\u0006". 

Tom\u00061:2021-04-01,60\t2021-04-07,62

What should I do to get the real white space and Unicode character?