Dear Experts,
Please provide a valuable solution for my problem.
I am having the fields from JSON which is having multivalue fields as below. In below example Department field having three values and Projects field having 5 values. I want expand this.
Name | EMP NO | Department | projects
ABCS | 1234567 | CS12345678 | PROJ1
| | AB12345678 | PROJ2
| | AB55555555 | PROJ3
| | | PROJ4
| | | PROJ5
I need output like the below
Name | EMP NO | Department | projects
ABCS | 1234567 | CS12345678 | PROJ1
ABCS | 1234567 | AB12345678 | PROJ2
ABCS | 1234567| AB55555555 | PROJ3
ABCS | 1234567 | NULL | PROJ4
ABCS | 1234567 | NULL | PROJ5
I start with the assumption that you have a single record that has Name="ABCS", EmpNo="1234567", Department= a multivalue field with three values, Projects= a multivalue field with 5 values.
your search here
| table Name EmpNo Department Projects
| streamstats count as recNo
| eval numRecs=If(mvcount(Department)>mvcount(Projects),mvcount(Department),mvcount(Projects)
| eval myFan=mvrange(0,numRecs)
| mvexpand myFan
| eval Department=case(myFan<mvcount(Department),mvindex(Department,myFan),
true(),"NULL")
| eval Projects=case(myFan<mvcount(Department),mvindex(Department,myFan),
true(),"NULL")
Now you have five separate records as requested.
The recNo
field is a record number in case you ever want to put them back together again. You can also use it (if desired) for break logic. That could look something like this...
your search here
| table Name EmpNo Department Projects
| streamstats count as recNo
| eval numRecs=If(mvcount(Department)>mvcount(Projects),mvcount(Department),mvcount(Projects)
| eval nextRec=numRecs+1
| eval myFan=mvrange(0,nextRec)
| mvexpand myFan
| eval Department=case(myFan<mvcount(Department),mvindex(Department,myFan),
myFan=numRecs,"",
true(),"NULL")
| eval Projects=case(myFan<mvcount(Department),mvindex(Department,myFan),
myFan=numRecs,"",
true(),"NULL")
| eval Name=case(myFan=numRecs,"",
true(),Name)
| eval EmpNo=case(myFan=numRecs,"",
true(),EmpNo)
Do post your json - it might be possible to extract the values correctly right away.
See the below question: same scenario