Arrange non-null values in a field

[rupesh26]

Hi,

I have the below events

100, ABC, , , 110, DEF, , , , , , , 
, ,120 ,GHI, 130, JKL, , , , , , ,
, ,140 ,MNO , , , , , , , 150,PQR ,

Ex: for the below event,
100, ABC, , , 110, DEF, , , , , , ,

these are the fields

Key_ID1="100"
Key_Value1="ABC"
Key_ID2=""
Key_Value2=""
Key_ID3="110"
Key_Value3="DEF"
Key_ID4=""
Key_Value4=""
Key_ID5=""
Key_Value5=""
Key_ID6=""
Key_Value6=""

I need to assign all these fields to a single field and move all null values to one side and not null values to the other side. something like below

100,ABC,110,DEF , , , , , , , , , 
120,GHI,130,JKL, , , , , , , , ,
140,MNO,150,PQR, , , , , , , , ,

then it has to be exported to a report.

I tried if conditions coalesce, nothing helped. Did someone tried this before ?

[jawaharas · Accepted solution]

Not efficient, but works! Play around.

| makeresults 
| eval myVar=",,140,MNO,,,,,,,150,PQR," 
| rex field=myVar mode=sed "s/,{2,}/,/g" 
| rex field=myVar mode=sed "s/^,|,$//g" 
| eval myVarCopy=myVar 
| makemv delim="," myVarCopy 
| eval myCount=mvcount(myVarCopy) 
`comment("by counting number of existing fields, remaining null fields are added as commas")`
| eval output=case(myCount=4,myVar . ",,,,,,,,", myCount=5,myVar . ",,,,,,,") 
| fields output

[rupesh26]

That Helped 🙂
Thanks