adding/appending a row to get differences between cells above

[HattrickNZ]

I have a search giving me a table with row 1 and 2 below:

    _time   A   B   C   D   
1   2015-02 1   3   5   7    
2   2016-02 2   4   6   8   
3    diff   2/1 4/3 6/5 8/7

I want to add row 3 giving me a value e.g 2/1=2 ... etc

How do I achieve this?

I know I can use delta if I was to add a column getting the differences but here I want to add a row with the difference in the column.

I thought appendpipe could be an option here but I think that just just works for getting max, min, sum....etc

[jeffland · Accepted solution]

This seems clumsy, but it works (the first line just sets up data to make this run anywhere).

| makeresults count=2 | streamstats count as a | eval a=a+1 | streamstats count as b
| appendpipe [ streamstats window=1 current=f last(b) as last_b last(a) as last_a | tail 1 | eval b=b/last_b | eval a=a/last_a | fields - last*]

[HattrickNZ]

tks,

that gives me

_time   a   b
1   2016-03-14 09:49:32 2   1
2   2016-03-14 09:49:32 3   2
3   2016-03-14 09:49:32 1.500000    2

but how do I control the 3rd row value for _time to be something else as opposed to 2016-03-14 09:49:32?

I could use this but I don't think the approach would work, unless know the values of b and c in row 3.

[jeffland]

You can either use | streamstats count | eval _time=if(count=="3", "something else", _time) for that if that's all you need.

[HattrickNZ]

tks but I got this to work by simply doing eval _time="something else" |

[jeffland]

That will change every line, but I'm glad it works for you.

[HattrickNZ]

tks but that did not matter, full example for reference. | makeresults count=2 | streamstats count as A | eval A=A+1 | streamstats count as b | eval b=b+10 | streamstats count as c | eval c=c+11 | appendpipe [ streamstats window=1 current=f last(b) as last_b last(a) as last_a last(c) as last_c | tail 1 | eval b=b/last_b | eval a=a/last_a | eval c=c/last_c | eval _time="something else" | fields - last*]