I have event data as follows: a,b,",1,2,3,",c,d
And I have lookup table as follows
key, value
1, one
2, two
3, three
4, four
I need the following output using lookup
a,b,",one,two,three,",c,d
note ",1,2,3," is not fixed in size.
Any help appreciated.
Try this...
| makeresults
| eval field1="a" | eval field2="b" | eval field3=",1,2,3," | eval field4="c" | eval field5="d"
| rename COMMENT as "The above enters your test data"
| rename COMMENT as "Deal with the leading comma, mark spots to split the data, split the data into separate records"
| rex mode=sed field=field3 "s/^,/((nothing)),/g s/,/,!!!!/g s/!!!!$//g"
| makemv delim="!!!!" field3
| mvexpand field3
| rename COMMENT as "Do the lookup, add a comma if found"
| lookup mylookup.csv field3 OUTPUT field3b
| eval field3=if(isnull(field3b),field3,field3b.",")
| fields - field3b
| rename COMMENT as "For run anywhere you can just delete the lookup to prove it works"
| rename COMMENT as "Recombine the records, join them, undo the fix for the leading comma, voila"
| mvcombine field3
| eval field3=mvjoin(field3,"!!!!")
| rex mode=sed field=field3 "s/\(\(nothing\)\),/,/g s/,!!!!/,/g"
Try this...
| makeresults
| eval field1="a" | eval field2="b" | eval field3=",1,2,3," | eval field4="c" | eval field5="d"
| rename COMMENT as "The above enters your test data"
| rename COMMENT as "Deal with the leading comma, mark spots to split the data, split the data into separate records"
| rex mode=sed field=field3 "s/^,/((nothing)),/g s/,/,!!!!/g s/!!!!$//g"
| makemv delim="!!!!" field3
| mvexpand field3
| rename COMMENT as "Do the lookup, add a comma if found"
| lookup mylookup.csv field3 OUTPUT field3b
| eval field3=if(isnull(field3b),field3,field3b.",")
| fields - field3b
| rename COMMENT as "For run anywhere you can just delete the lookup to prove it works"
| rename COMMENT as "Recombine the records, join them, undo the fix for the leading comma, voila"
| mvcombine field3
| eval field3=mvjoin(field3,"!!!!")
| rex mode=sed field=field3 "s/\(\(nothing\)\),/,/g s/,!!!!/,/g"
Thanks tons. It worked as I wanted.