Need to build a query

Hello All, I want to measure the lower limt and upper limt point when my graph is forming the first curve. And then I want to calculate the difference between them. I want to see that how my graph is change from point 1 to point 2 (graph changing pattern) . Is it possible to write such query: I am using this code but it’s Incomplete:

`from(bucket: "Fog")`
`  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)`
`  |> filter(fn: (r) => r["_measurement"] == "SensorInformation")`
`  |> filter(fn: (r) => r["_field"] == "SensorNumber" or r["_field"] == "SensorValue")`
`  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") `
`  |> filter(fn: (r) => r.SensorNumber == 1 )`
`  |> filter(fn: (r) => r.SensorValue > 0 )`
`  |>group(columns: ["SensorNumber"])`

And In the graph After the first curve rest, all the data where peak goes are garbage value. I am Interested to get information from the first curve. Here is the graph:

Hello @Nitesh,
I’m not sure why you’re pivoting since you only have one line or field value pair.
I would probably use derivative and then filter for when the _value = 0.
then I would calculate the duration between points with events.duration()
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/derivative/
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/contrib/events/duration/

1 Like

Hello @Anaisdg , Thankyou so much for your reply.
Here I am using Two fields, Sensor Number (having eight sensor) and the other is Sensor Values.
So I used the pivot().

I tried to built the query as per your approach, Here is the query that I used:

import "contrib/tomhollingworth/events"
from(bucket: "Fog")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "SensorInformation")
  |> filter(fn: (r) => r["_field"] == "SensorNumber" or r["_field"] == "SensorValue")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") 
  |> filter(fn: (r) => r.SensorNumber == 1 )
  |> derivative(unit: 1s, nonNegative: true, columns: ["SensorValue"],timeColumn: "_time")
  |> filter(fn: (r) => r.SensorValue == 0 )
 |> events.duration(unit: 1s,columnName: "duration",timeColumn: "_time",stopColumn: "_stop",)

The output seems to be like this:

It looks like that what I want to achieve may be possible to get with Influx.

My expectation is that when Influxdb find this maked graph (pattern) as in figure then sould give me the maximum and minimum from the patten and find the diffrence between the two.
Also I think your suggested approach is good . I will try more with this. Really thanykou so much. If you get any suggestion then please let me know.

1 Like

@Anaisdg May be de can define with event.duration() the state of the duration before the circle as “No Process” In circle as “Process” and then After Circle as “No process”. But how to give contion for this , I am getting no Idea. Also if It recieve again after same time the same graph pattern then must do the same again.

Hello @Nitesh,
I’m sorry I didn’t think of this earlier. You might also be interested in using:
https://docs.influxdata.com/influxdb/cloud/reference/flux/stdlib/built-in/transformations/aggregates/spread/

you can give condition with map:
https://docs.influxdata.com/influxdb/cloud/query-data/flux/conditional-logic/#conditionally-transform-column-values-with-map

You can do this work in a task, execute it on a schedule, and write the output to a new measurement.
https://docs.influxdata.com/influxdb/cloud/process-data/
Basically you can copy your query into a task all you need to do is add the to() funciton.
If you want to write the data to a new bucket you just need to specify the new bucket you want to write to.
If you want to write the data to a new measurement in the same bucket, then you need to use the map() or set() function to rename your measurement.

|> set(key: "_measurement",value: "newMeausrementName")

Make sure to include an offset to avoid read and write conflicts.

The easiest way to get started creating a task is through the UI or CLI.

Please let me know if you need more help and way to go on getting the Flux to work so far!

Hello @Anaisdg, Thank you for the reply. Actually I do not condition with me otherwise I could map it using condition. I come to know the process and non-process time by seeing the sensor physically and at the same time in the graph which is the first curve that you see in the figure. Also, I will try using what you suggested and will let you know. Thank you so much !!

@Nitesh absolutely! I encourage you to share the Flux that works for you so other community members can benefit from your questions and work :slight_smile: Thank you!

1 Like

Hello @Nitesh,
I used aggregateWindow() and spread() to

give me the maximum and minimum from the patten and find the diffrence between the two.

common = from(bucket: "Air sensor sample dataset")
  |> range(start: 2021-08-19T19:23:37.000Z, stop: 2021-08-19T19:24:17.000Z)
  |> filter(fn: (r) => r["_measurement"] == "airSensors")
  |> filter(fn: (r) => r["_field"] == "co")
  |> filter(fn: (r) => r["sensor_id"] == "TLM0100" or r["sensor_id"] == "TLM0101")
  |> pivot(rowKey:["_time"], columnKey: ["sensor_id"], valueColumn: "_value") 
  |> limit(n: 5)
  |> yield(name: "common")

TLM0100 = common 
  |> aggregateWindow(every: 20s, fn: spread, column: "TLM0100")
  |> yield(name: "(max-min) of the sensor TLM0100")
  
TLM0101 = common 
  |> aggregateWindow(every: 1d, fn: spread, column: "TLM0101")
  |> yield(name: "(max-min) of the sensor TLM0101")

https://docs.influxdata.com/influxdb/cloud/reference/flux/stdlib/built-in/transformations/aggregates/spread/
https://docs.influxdata.com/influxdb/cloud/reference/flux/stdlib/built-in/transformations/aggregates/aggregatewindow/