Compare values of two rows

Hi!

I want to compare the values of two rows in the same resultset. How to achieve this using Flux?
For example, I take the last two rows with tail(2) and want to compare the values of both rows. If the values are equal do nothing, else send a notification.

I hope you could help me with this.

Br, Tobias

@he4d You could use tail() function to get the last n rows from the table. Then apply the difference() function on it. Then you can use a conditional expression to check the result of the difference and send a notification accordingly.

1 Like

@skartikey Thank you very much!
But what about if this is a string value and I want to compare details of both strings? difference() does not work on strings.

Br, Tobias

@he4d in that case you could use strings.compare() function

@skartikey Perfect!
Is it somehow possible to do this in one query?
Or do I need to create separate variables for the first and the last value of the tail(2) result and compare these?

@he4d yes you could do that in single query using pivot() function
here is the pseudo query (you have to change fields according to your data)

basic filters ... 
  |> tail(2)
  |> pivot(
    rowKey:["_time"],
    columnKey: ["_field"],
    valueColumn: "_value"
  )
  |> map(fn: (r) => ({
    if strings.compare(v: r.c1, t: r.c2) !=0 
      then true 
      else false
  }))

@skartikey
The only problem I have with your solution is r.c1 and r.c2. Can I access the first row with r.c1 and the second one with r.c2 or what is meant with these?

I get the following error message
runtime error @8:8-17:6: map: failed to evaluate map function: expected argument "v" to be of type string, got type invalid value {}

The complete query looks like this:

import "strings"

from(bucket: "Testbucket")
    |> range(start: -10h)
    |> filter(fn: (r) => r["_measurement"] == "movement" and r["sub_area"] == "test")
    |> tail(n: 2)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(
        fn: (r) =>
            ({
                _value:
                    if strings.compare(t: r.c1, v: r.c2) != 0 then
                        true
                    else
                        false,
            }),
    )

@he4d c1 and c2 are just random names I have used for the column. In your case, it will be something else. Use yield function to see the result and use col names from there.

This will give you the results after the pivot function.

from(bucket: "Testbucket")
    |> range(start: -10h)
    |> filter(fn: (r) => r["_measurement"] == "movement" and r["sub_area"] == "test")
    |> tail(n: 2)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> yield()

Kindly check some examples here for pivot and map

@skartikey

To get what I need I need to take the _time as columnKey as I want to compare the old and the new value.
For a better understanding of what I need I added an screenshot of the current result. Please see the notes I wrote to the last two columns.

If I could get these named old_value and new_value I would be able to access these columns and compare the csv content in the row