a | b | c | d | e | f | g |
xyz | 1 | 2 | 3 | 4 | 5 | 6 |
My table looks like that
I need the following table
a | b | c | d | e | f | g |
xyz | 1000.00 | 2000.00 | 3000.00 | 4000.00 | 5000.00 | 6000.00 |
but with the below command I am getting the following table :
my string field gets eliminated
my query for data | foreach * [eval <<FIELD>> = round(('<<FIELD>>' * 1000),2) ]
a | b | c | d | e | f | g |
1000.00 | 2000.00 | 3000.00 | 4000.00 | 5000.00 | 6000.00 |
How to obtain everything above along with xyz in a column ?
Firstly, I'm not a big fan of truncating fields or modifying them in any way without a good cause so I'd rather use fieldformat rather than eval (especially if you want to calculate something down the processing stream based on the original values; truncating introduces rounding error).
But I'm digressing.
The easiest thing to do is use coalesce to add the original value in case your computations turned null.
eval <<FIELD>> = coalesce(round('<<FIELD>>'*1000,2),'<<FIELD>>')
As coalesce() evaluates to the first non-null value from the list, for numerical values you'll get your number, for other values you'll get the original field contents.
The coalesce command isn't working, its printing all the values as it is
Works for me.
See this runanywhere example:
Sorry, My Bad !!
Worked for me too, it was an error on my part.
Can you also please provide me a solution with field formatting, will keep that in mind for future use cases.
The fieldformat syntax is almost the same as eval. The difference is that fieldformat doesn't create a new field as such. So you will see the "field" in output but you cannot calculate anything based on its value - you have to use the original field.
See the example (also shows the difference between eval and fieldformat):
| makeresults count=210
| streamstats count
| eval part=count/13
| eval truncated=round(part,1)
| fieldformat formatted=round(part,1)
| eventstats sum(truncated) as truncatedsum sum(part) as partsum
In case of simple multiplying when there's no rounding error, there's not much of a practical difference since the value itself does not differ but in case of fractions the error can accumulate and skew the final results.
The most useful use case of field format is with timestamp manipulation when you leave the original timestamp field in numerical form which allows for easy adding/substracting intervals and so on but still can get a human-readable representation in the results.
Thanks for the solution.
Can you also please provide the solution with FEILDFORMAT command ? 🙂 😀