How to merge different values ​​based on one field?

[jinnypt]

I have a table like this one, and I want to know how to merge different values ​​based on one field.

example table)

[AS-IS]

[TO-BE]

ps. a/b/c is the value when at/bt/ct is "Y"

[renjith_nair · Accepted solution]

@jinnypt,

Does this work for you ?

"your search terms for getting the fields "
| streamstats current=f window=1 last(at) as prev_at, last(bt) as prev_bt,last(ct) as prev_ct,count as rownum by name reset_on_change=true
| eval at=coalesce(at,prev_at),bt=coalesce(bt,prev_bt),ct=coalesce(ct,prev_ct)
| eval a=if(at=="ok","Y","N"),b=if(bt=="ok","Y","N"),c=if(ct=="ok","Y","N")
| where rownum=1
| table name,a,b,c,at,bt,ct

Or run anywhere example with if instead of coalesce in case you have "" instead of a null value

| makeresults
| eval name="1,1,2,2"| makemv delim="," name
| eval a="N,Y,Y,N"| makemv delim="," a| eval b="Y,N,N,N"| makemv delim="," b| eval c="N,N,N,Y"| makemv delim="," c
| eval at="nil,ok,ok,nil"| makemv delim="," at|eval bt="ok,nil,nil,nil"| makemv delim="," bt
| eval ct="nil,nil,nil,ok"| makemv delim="," ct
| eval t=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(name,a,"#"),b,"#"),c,"#"),at,"#"),bt,"#"),ct,"#")
| table t| mvexpand t| eval z=split(t,"#")
| eval name=mvindex(z,0),a=mvindex(z,1),b=mvindex(z,2),c=mvindex(z,3),at=mvindex(z,4),bt=mvindex(z,5),ct=mvindex(z,6)
| fields name,a,b,c,at,bt,ct| replace nil with ""
| eval _COMMENTS="=-=-=-=-=-=-=-=-=UNTIL HERE IS DUMMY DATA GENERATION AND NOTHING HAS TO DO WITH ACTUAL SEARCH =-=-=-="
   | streamstats current=f window=1 last(at) as prev_at, last(bt) as prev_bt,last(ct) as prev_ct,
      count as rownum by name reset_on_change=true
   | where rownum=1
   | eval at=if(at="",prev_at,at),bt=if(bt="",prev_bt,bt),ct=if(ct="",prev_ct,ct)
   | eval a=if(at=="ok","Y","N"),b=if(bt=="ok","Y","N"),c=if(ct=="ok","Y","N")
   | table name,a,b,c,at,bt,ct