How to globally replace a value in any of the fields.

[jiaqya]

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..

[manjunathmeti]

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>>)]

[jiaqya]

i do have my solution by using replace 0 as NA

[richgalloway · Accepted solution]

Have you tried | replace 0 with "NA" | ?

[jiaqya]

This will replace the other 0 also to NA, like in 20,40 etc... and the column names are dynamic...

[anmolpatel]

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"

[jiaqya]

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.