how to get difference between one value and the other values in a particular column or row?

[pramit46]

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.

[nryabykh · Accepted solution]

Hi! Try this:

%your search%
| eventstats first(A) as firstA
| eval C = firstA - A

[pramit46]

Awesome!!
Thanks @nryabykh, can we also do it row-wise? I doubt, if it is possible.

[nryabykh]

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?

[pramit46]

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.

[nryabykh]

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]