Input Table:
order-no order-date currency country-code quantity(MV) base-price(MV) net-price(MV) product-id(MV) customer-no adjust-coupon-id(MV)
1 2017-07-31T13:13:31.000Z AUD AU 1.0 30.00 27.27 2 123 BFFYAY
1.0 50.00 45.45 3 BFFYAY
1.0 45.00 40.91 4 BFFYAY
1.0 45.00 40.91 5 <NO VALUE>
Current Query to Flatten:
(index=* OR index=_*) (index="myindex" "order{@order-no}"="1" "order.customer.customer-no"="*")
| rename order{@order-no} as "order-no",
order.order-date as "order-date",
order.currency as "currency",
order.shipments.shipment.shipping-address.country-code as "country-code",
order.product-lineitems.product-lineitem.product-id as "productid",
order.product-lineitems.product-lineitem.quantity as "quantity",
order.product-lineitems.product-lineitem.base-price as "baseprice",
order.product-lineitems.product-lineitem.net-price as "netprice",
order.product-lineitems.product-lineitem.price-adjustments.price-adjustment.promotion-id as "adjustpromotionid",
order.product-lineitems.product-lineitem.price-adjustments.price-adjustment.campaign-id as "adjustcampaignid",
order.product-lineitems.product-lineitem.price-adjustments.price-adjustment.coupon-id as "adjustcouponid",
order.product-lineitems.product-lineitem.price-adjustments.price-adjustment.lineitem-text as "adjustlineitemtext",
order.product-lineitems.product-lineitem.price-adjustments.price-adjustment.base-price as "adjustbaseprice",
order.product-lineitems.product-lineitem.tax as "tax",
order.customer.customer-no as "customer-no"
| eval combine=mvzip(mvzip(mvzip(mvzip(coalesce(productid,""),coalesce(quantity,""),"!!#@#!!"),coalesce(baseprice,""),"!!#@#!!"),coalesce(netprice,""),"!!#@#!!"),coalesce(adjustcouponid,""),"!!#@#!!")
| mvexpand combine
| eval allvalues=split(combine,"!!#@#!!")
| eval "adjust-coupon-id"=mvindex(allvalues,4)
| eval "net-price"=mvindex(allvalues,3)
| eval "base-price"=mvindex(allvalues,2)
| eval "quantity"=mvindex(allvalues,1)
| eval "product-id"=mvindex(allvalues,0)
| table "order-no", "order-date", "currency", "country-code","quantity", "base-price", "net-price", "product-id", "customer-no", "adjust-coupon-id"
Result:
order-no order-date currency country-code quantity base-price net-price product-id customer-no adjust-coupon-id
1 2017-07-31T13:13:31.000Z AUD AU 1.0 30.00 27.27 2 00436933 BFFYAY
1 2017-07-31T13:13:31.000Z AUD AU 1.0 50.00 45.45 3 00436933 BFFYAY
1 2017-07-31T13:13:31.000Z AUD AU 1.0 45.00 40.91 4 00436933 BFFYAY
... View more