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?
... View more