So I am trying to figure out how to separate out multi value fields of different lengths. The problem is that mvzip will use the previous value when one value runs out instead of placing null. For example
If I have
TransID ProductID ProductPropertyB ProductPropertyC ProductPropertyD
001 P003 B1 C1 D1
P004 B2 C3 D2
P005 B4 C2
P006 B2 C2
002 P003 B2 C1 D3
P005 B4 C2 D3
Using the following code
| eval reading=mvzip(ProductID, ProductPropertyB)
| eval reading=mvzip(reading, ProductPropertyC)
| eval reading=mvzip(reading, ProductPropertyD)
| mvexpand reading
| makemv reading delim=","
| eval ProductID=mvindex(reading, 0)
| eval ProductPropertyB=mvindex(reading, 1)
| eval ProductPropertyC=mvindex(reading, 2)
| eval ProductPropertyD=mvindex(reading, 3)
| table TransID,ProductID,ProductPropertyB,ProductPropertyC,ProductPropertyD
evaluates to
TransID ProductID ProductPropertyB ProductPropertyC ProductPropertyD
001 P003 B1 C1 D1
001 P004 B2 C3 D2
001 P005 B4 C2 D2
001 P006 B2 C2 D2
002 P003 B2 C1 D3
002 P005 B4 C2 D3
when I want
TransID ProductID ProductPropertyB ProductPropertyC ProductPropertyD
001 P003 B1 C1 D1
001 P004 B2 C3 D2
001 P005 B4 C2 Null
001 P006 B2 C2 Null
002 P003 B2 C1 D3
002 P005 B4 C2 D3
So how can I achieve this?
Give this a try
your current search giving first output
| fillnull value="NULL"
| eval reading=mvzip(ProductID, ProductPropertyB)
| eval reading=mvzip(reading, ProductPropertyC)
| eval reading=mvzip(reading, ProductPropertyD)
| mvexpand reading
| makemv reading delim=","
| eval ProductID=mvindex(reading, 0)
| eval ProductPropertyB=mvindex(reading, 1)
| eval ProductPropertyC=mvindex(reading, 2)
| eval ProductPropertyD=mvindex(reading, 3)
| table TransID,ProductID,ProductPropertyB,ProductPropertyC,ProductPropertyD
| foreach * [eval <<FIELD>>=if('<<FIELD>>'="NULL",null(),'<<FIELD>>') ]
Hi,
I put your original data from the question into a CSV for testing, then did:
| from inputlookup:"test-mvzip.csv"
| table TransID,ProductID,ProductPropertyB,ProductPropertyC,ProductPropertyD
| streamstats last(TransID) as TransID
Which produces the table:
You'd want to test it with your original data and Search. But I hope that helps.