MVExpand help

[rlautman]

I asked a question earlier regarding the preformatting of a csv report which several multivalue fields (Preformat Automatic Report - CSV) and was given the suggestion to use mvexpand. I believe that mvexpand will help with the formatting of my report but I have noticed that it seems to work with only 1 field - when I use multiple mvexpand statements I experience the error of constantly repeating lines in my table - for example: my fields could be Order ID, Product, OrderStatus, CompletionCode - OrderID will have only one item but each field after this could have up to 6 items which correspond to each other, i.e.

OrderID Product OrderStatus CompletionCode

1234-56 TV Accepted 567

Aerial Accepted 567

Phone Cancelled 890

When I try to use an mvexpand statement for each field, the first item in the Product field and the data in corresponding fields, seems to continuously repeat and none of the other fields, including other order results, appear

[martin_mueller]

There is a slightly better way, but it's not perfect either - requires OrderID to be unique, if it is not you need to do another count before the mvexpand to group by that count:

... | mvexpand Product | streamstats current=f count by OrderID | eval OrderStatus = mvindex(OrderStatus, count) | eval CompletionCode = mvindex(CompletionCode, count) | fields - count

However, you might make it all much easier if you can influence the report itself, to stop it from having multi-valued fields in the first place... that depends on where your data comes from and how you process it.

[sideview]

I believe this is what mvzip is for, although it's not intuitive at first as to why, and the following is kind of a long way to go for something that feels like it should be simpler. Maybe someone has a better way, but here goes.

mvzip can take two fields at a time, say Product and OrderStatus, and zip up the multivalue pairs, creating one multi-valued-field which we might call "ProductAndOrderStatus", whose values are each a comma-separated pair of Product and OrderStatus .

If we were to do the same thing a second time we could combine our "ProductAndOrderStatus" field with the "CompletionCode" field, and then if we use mvexpand on the aggregate field, we'll get the right number of rows, and then with some careful use of split and mvindex, we can get our three fields teased apart again.

| eval ProductAndOrderStatus=mvzip(Product,OrderStatus) | eval finalCombined=mvzip(ProductAndOrderStatus,CompletionCode) | fields - Product OrderStatus CompletionCode | mvexpand finalCombined | eval finalCombined=split(finalCombined,",") | eval Product=mvIndex(finalCombined,0) | eval OrderStatus=mvindex(finalCombined,1) | eval CompletionCode=mvindex(finalCombined,2) | fields - finalCombined

I'm kind of giving this answer hoping that someone has a better way, but at least this is a way that I know will work.

[sideview]

Strange. I'm not sure what's happened there. When there's N values of Product in a given row are there always exactly N values of OrderStatus and CompletionCode?

[rlautman]

I have tried this but now have got a number of rows containing only the OrderID - but none of the tags in the xml are empty