How to compare last value with the 7th last value?

[aguasd12]

How to compare last value with the second last value?

Say I have a column with N records in it
88
22
67. -->
44
55
12
44
75
80 -->

I want to compare the last record 80 with that of 67( last value and want to write whether the value was 'greater' or 'smaller' in the output.

In above case 55 was greater so my output should say GREATER. Do we have any command to accomplish this?

[yuanliu]

---

How to compare last value with the second last value?

....

I want to compare the last record 80 with that of 67( last value and want to write whether the value was 'greater' or 'smaller' in the output.

In above case 55 was greater so my output should say GREATER. ...

---

You may need to describe the use case with consistency.  The title says last vs 7th last, which kinda agrees with 80 vs 67.  Then, the opening sentence says last with second last; and the last sentence cites a number 55 which is neither the 7th last nor the second last.

Here, I'll take 7th last.  The following statement also needs clarification:

---

Say I have a column with N records in it

---

Does this mean that the column is multivalued with 7 records, or does it mean that you have 7 separate events with this column?

If former, a semantical interpretation of your requirement (compare with 7th last) is

| eval output = if(tonumber(mvindex(column, -1)) - tonumber(mvindex(column, -7)) > 0, "GREATER", "SMALLER or EQUAL")

If latter, you first make the column multivalued with original sequence.

| stats list(column) as column
| eval output = if(tonumber(mvindex(column, -1)) - tonumber(mvindex(column, -7)) > 0, "GREATER", "SMALLER or EQUAL")

Here, I also do not know if you want EQUAL to be a separate output so I'm using the simplest if() function.  If you need a separate EQUAL, it would be more economic to save output of mvindex to variables (fields) before applying case() command.

Hope this helps.

[yeahnah]

Hi @aguasd12 

An interesting use case.  Something like this should work...

| makeresults
| eval n=mvrange(0,80)
| mvexpand n
| eval n=substr(tostring(n * random()), 1, 2)
  ``` above just creates dummy results ```
  ``` add the following to you query ```
| streamstats count
| where count=67 OR count=80
| table count n
| transpose header_field="count"
| eval status=case(('67'=='80'), "EQUAL", ('67' > '80'), "GREATER THAN", ('67' < '80'), "LESS THAN")

Hope that helps

[yeahnah]

On rereading your question I see I misunderstood it, initially.  This should meet you use case 

| makeresults
| eval n=mvrange(0,100)
| mvexpand n
| eval n=substr(tostring(n * random()), 1, 2)
  ``` above just creates dummy results ```
  ``` add the following to your query ```
| streamstats count
| eventstats max(count) as total_rows
| eval 7thlast=(total_rows-7)
| where count='7thlast' OR count='total_rows'
| table count n 7thlast total_rows
| transpose
| where column="n"
| rename "row 1" AS 7thlast, "row 2" AS last
| eval status=case(('7thlast'=='last'), "EQUAL", ('7thlast' > 'last'), "GREATER THAN", ('7thlast' < 'last'), "LESS THAN")