Difference between rows of query result

[reverse]

Jan-1 100 60 87 78 86 545 53 509 56 545 656
Jan2  110 60 87 78 86 545 53 509 56 545 656
Jan-3 111 60 87 78 86 545 53 509 56 545 655
Jan-4 112 60 89 78 86 545 53 509 56 545 656

diff 2 0 2 0 ....
I have to compute "always" the difference between last row and first row ( diff)
How can I achieve this ?
Thanks

[niketn · Accepted solution]

@reverse try the following run anywhere example which prepares data similar to your question. from |makeresults till | fields - data count

| makeresults
| eval data="Jan-1 100 60 87 78 86 545 53 509 56 545 656;Jan-2 110 60 87 78 86 545 53 509 56 545 656;Jan-3 111 60 87 78 86 545 53 509 56 545 655;Jan-4 112 60 89 78 86 545 53 509 56 545 656"
| makemv data delim=";"
| stats count by data
| makemv data delim=" "
| eval date=mvindex(data,0),
       field1=mvindex(data,1),
       field2=mvindex(data,2),
       field3=mvindex(data,3),
       field4=mvindex(data,4),
       field5=mvindex(data,5),
       field6=mvindex(data,6),
       field7=mvindex(data,7),
       field8=mvindex(data,8),
       field9=mvindex(data,9),
       field10=mvindex(data,10)
| fields - data count

| fields - date
| stats first(*) as first* last(*) as last*
| foreach first* [| eval diff_<<MATCHSTR>>=first<<MATCHSTR>>-last<<MATCHSTR>>]
| fields diff_*

Then the remaining command calculate difference as per your requirement. Since you have not provided field names I have cooked up all of it as field1, field2 etc.

[reverse]

it worked.. thanks! how can i show only that data where diff was maximum... like top 2.. I know it is complex

[reverse]

how can i show only that data where diff was maximum... like top 2..

[niketn]

@reverse try appending the following to your existing search.

| transpose 0 column_name=difference
| sort 0 - "row 1"
| head 2
| transpose header_field=difference
| fields diff_*

[reverse]

results would be dynamic.. first column

[niketn]

@reverse please add more details to your problem. For the data provided what is the output you need?

[reverse]

diff is the output

[niketn]

@reverse the number of rows is it fixed or can it vary? Also once you have the difference do you want to output only the difference?

[reverse]

Rows will vary as per timepicker range .. last 7 days 30 days .. so on .. columns are fixed though