my data is like the table below. Column C is what I need to calculate:
A----B----C
10----12----?
25----20----?
23----12----?
What I need in the column C is the difference between the first value and the all the values (including itself) in column A. So the values of the column C should be
10-10=
0
10-25=
-15
10-23=
-13
Any clue, how to do it? Please note that, Column B should not be included in this calculation.
I would also like to know the same can be done row-wise.
Not sure if the question statement clarifies the problem, if not, let me know in the comments, I'll try to clarify it further.
Hi! Try this:
%your search%
| eventstats first(A) as firstA
| eval C = firstA - A
Hi! Try this:
%your search%
| eventstats first(A) as firstA
| eval C = firstA - A
Awesome!!
Thanks @nryabykh, can we also do it row-wise? I doubt, if it is possible.
I'm sorry, i don't fully understand what do you mean as "row-wise". Column C filled per each row, so it could be considered as "row-wise", I believe. Could you clarify your question a little more, maybe with an example?
okay. let's say, in the following dataset:
A----B----C
10----12----5
25----20----15
23----12----25
?----?----?
if I want to fix the first Row and populate the last row, then can I do it?
so in this case the last row would be:
(10-10=)0----(10-12=)-2----(10-5=)5
Basically the same thing, but row-wise.
I think this can be achieved by doing transpose, but with too many rows, transpose may not work well. Hence, asking.
Well, I managed to do this, but it's a bit tricky 🙂
%your_search%
| eventstats first(A) as firstA
| streamstats count as cnt
| foreach A, B, C
[eval sub_<<FIELD>>=if(cnt=1, firstA-<<FIELD>>, null)]
| addcoltotals labelfield="cnt" label="newrow"
| foreach A, B, C
[eval <<FIELD>>=if(cnt="newrow", sub_<<FIELD>>, <<FIELD>>)]
| fields - cnt, firstA, sub*
If your initial search is a quite light, you can use append
command. The cons are that you have to duplicate your initial search in append's subsearch:
%your_search%
| append
[%your_search%
| eventstats first(a) as firstA
| head 1
| foreach A, B, C
[eval <<FIELD>>=firstA-<<FIELD>>]
| fields - firstA]