split 2 multivalue fields concurrently/ split 1 multivalues row into multiple row

[yuming1127]

Hello,

Currently encounter 1 issue which unable to split 2 multivalues fields concurrently.

my table:

Product	color	description1	description2
phone	blue	location : loc_a date: date_a serial_no: serial_a	location : loc_a date: date_q
phone	yellow	location : loc_d date: date_e serial_no: serial_f	location : loc_x date: date_y serial_no: serial_z

desired output:

Product	color	description1	description2
phone	blue	location : loc_a	location : loc_a
phone	blue	date:date_a	date: date_q
phone	blue	serial_no: serial_a
phone	yellow	location : loc_d	location : loc_x
phone	yellow	date: date_e	date: date_y
phone	yellow	serial_no: serial_f	serial_no: serial_z

im able to find a function called  mvexpand but sadly it cannot split 2 field.

Regards,

Yu Ming

[yuming1127]

@ITWhisperer  , i discovered the solution not work correctly for below case.

Product	color	description1	description2
phone	blue	location : loc_a date: date_a serial_no: serial_a extra_info_a:info_a extra_info_b:info_b	location : loc_a date: date_q extra_info_a: info_c extra_info_b: info_d
phone	yellow	location : loc_d date: date_e serial_no: serial_f	location : loc_x date: date_y serial_no: serial_z

Current output: where there are mismatch of field

Product	color	description1	description2
phone	blue	location : loc_a	location : loc_a
phone	blue	date:date_a	date: date_q
phone	blue	serial_no: serial_a	extra_info_a: info_c
phone	blue	extra_info_a:info_a	extra_info_b: info_d
phone	blue	extra_info_b:info_b
phone	yellow	location : loc_d	location : loc_x
phone	yellow	date: date_e	date: date_y
phone	yellow	serial_no: serial_f	serial_no: serial_z

Cannot figure out how to do that, would appreciate if there a clear procedure/solution i can do, thanks

[ITWhisperer]

It would have helped if you had made it clearer that the "keys" in the multi-value field had to align!

Try this

| makeresults 
| eval _raw="Product,color,description1,description2
phone,blue,location: loc_a|date: date_a|serial_no: serial_a|extra_info_a: info_a|extra_info_b: info_b,location: loc_a|date: date_q|extra_info_a: info_c|extra_info_b: info_d
phone,yellow,location: loc_d|date: date_e|serial_no: serial_f,location: loc_x|date: date_y|serial_no: serial_z
phone,green,location: loc_p|date: date_q,location: loc_g|date: date_h|serial_no: serial_i"
| multikv forceheader=1
| eval description1=split(description1,"|")
| eval description2=split(description2,"|")
| fields - _* linecount



| eval keys1=mvmap(description1,mvindex(split(description1,":"),0))
| eval keys2=mvmap(description2,mvindex(split(description2,":"),0))
| eval keys=mvdedup(mvappend(keys1,keys2))
| eval range=mvrange(0,mvcount(keys))
| mvexpand range
| eval description1=mvindex(description1,mvfind(keys1,mvindex(keys,range)))
| eval description2=mvindex(description2,mvfind(keys2,mvindex(keys,range)))
| table Product color description1 description2

[yuming1127]

Its worked, great that learned mvmap and mvfind today, thanks alot

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="Product,color,description1,description2
phone,blue,location : loc_a|date: date_a|serial_no: serial_a,location : loc_a|date: date_q
phone,yellow,location : loc_d|date: date_e|serial_no: serial_f,location : loc_x|date: date_y|serial_no: serial_z
phone,green,location : loc_p|date: date_q,location : loc_g|date: date_h|serial_no: serial_i"
| multikv forceheader=1
| eval description1=split(description1,"|")
| eval description2=split(description2,"|")
| fields - _* linecount



| eval range=mvrange(0,max(mvcount(description1),mvcount(description2)))
| mvexpand range
| eval description1=mvindex(description1,range)
| eval description2=mvindex(description2,range)