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
| 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)
@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
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
| 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)