# Create column based on previous row value

Hello together, I’m trying to create a query that operates on a given table. The goal is to add a new column `val` that calculates the difference between the current and the previous value of column `_value` if the current value is bigger than the previous and `_field` of the current row is equal to `_field` of the previous row. The following is a minimal example:

``````Input:
+---------------------+--------+--------+
|        _time        | _field | _value |
+---------------------+--------+--------+
| 2021-10-24 02:00:00 | x      |      0 |
| 2021-10-24 02:10:00 | x      |      2 |
| 2021-10-24 02:20:00 | x      |      1 |
| 2021-10-24 02:00:00 | y      |      3 |
| 2021-10-24 02:10:00 | y      |      3 |
| 2021-10-24 02:20:00 | y      |      4 |
+---------------------+--------+--------+
``````
``````Output:
+---------------------+--------+--------+-----+
|        _time        | _field | _value | val |
+---------------------+--------+--------+-----+
| 2021-10-24 02:00:00 | x      |      0 |   0 |
| 2021-10-24 02:10:00 | x      |      2 |   2 |
| 2021-10-24 02:20:00 | x      |      1 |   0 |
| 2021-10-24 02:00:00 | y      |      3 |   0 |
| 2021-10-24 02:10:00 | y      |      3 |   0 |
| 2021-10-24 02:20:00 | y      |      4 |   1 |
+---------------------+--------+--------+-----+
``````

How would I achieve this within the Flux language?

I would use something like

``````data
|> duplicate(column: "_value", as: "val")
|> difference(columns: ["val"], nonNegative: true, keepFirst: true)
|> fill(column: "val", value: 0)

``````

Thanks for your answer. Your code works. But how does it know that the subtraction between row 3 and row 4 should not be calculated? The code doesn’t mention that if the value of `_field` is not equal to the previous value, then the difference should be set to zero. However, it still works. Why?

I think the subtraction is always calculated, if you try it without `nonNegative: true` and `fill()`, you will see.