i have a output where i have 0 in random columns.
i would like these 0's to be replaced with any text for reporting...
is it possible to replace 0 in any field ? ex output below
Jan2019 Feb2019 Mar2019 Apr2019
0 1 8 10
1 20 3 40
9 1 0 4
the above should change to
Jan2019 Feb2019 Mar2019 Apr2019
NA 1 8 10
1 20 3 40
9 1 NA 4
there are also 0's in other cells but they are not =0, but 10 ,40 , 20 etc...
but only =0 should be replaced, thanks for the response in advance..
Have you tried | replace 0 with "NA" |
?
hi @jiaqya,
Try this:
| foreach Jan2019,Feb2019,Mar2019,Apr2019 [ eval <<FIELD>>=if(<<FIELD>>==0, "NA", <<FIELD>>)]
If the interested fields contains same string in their names:
| foreach *2019 [ eval <<FIELD>>=if(<<FIELD>>==0, "NA", <<FIELD>>)]
Sample query:
| makeresults
| eval _raw="Jan2019,Feb2019,Mar2019,Apr2019
0,1,8,10
1,20,3,40
9,1,0,4"
| multikv forceheader=1
| fields - _time,_raw,linecount
| foreach Jan2019,Feb2019,Mar2019,Apr2019
[ eval <<FIELD>>=if(<<FIELD>>==0, "NA", <<FIELD>>)]
i do have my solution by using replace 0 as NA
Have you tried | replace 0 with "NA" |
?
This will replace the other 0 also to NA, like in 20,40 etc... and the column names are dynamic...
richgalloway's solution will work. replace will not modify values like in 20, 40 etc. That will only happen if you specify |replace *0 with "NA"
Right, i was trying rex before and it was replacing 0 as NA everywhere it found.
with replace 0 with "NA" , it is only replacing the 0 , and not the 20/40
thanks, this solves my problem.
john.