# Need to build a query **URL:** \ **Category:** InfluxDB 2\ **Tags:** influxdb, query, flux\ **Created:** [September 11, 2021, 8:50am UTC](https://community.influxdata.com/t/need-to-build-a-query/21663 "2021-09-11T08:50:56Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Nitesh](https://avatars.discourse-cdn.com/v4/letter/n/8491ac/32.png) [@Nitesh](https://community.influxdata.com/u/Nitesh)\ **Post date:** [September 11, 2021, 8:50am UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/1 "2021-09-11T08:50:56Z")
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: ```auto `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: ![SensorData](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/c/c452b7ae32a384c17e8b5945085493d07f643124.png) ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [September 13, 2021, 7:03pm UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/2 "2021-09-13T19:03:59Z")
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() > **[derivative() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/universe/derivative/)** > > derivative() computes the rate of change per unit of time between subsequent non-null records. > **[events.duration() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/contrib/tomhollingworth/events/duration/)** > > events.duration() calculates the duration of events. ---
**Author:** ![Nitesh](https://avatars.discourse-cdn.com/v4/letter/n/8491ac/32.png) [@Nitesh](https://community.influxdata.com/u/Nitesh)\ **Post date:** [September 14, 2021, 9:36am UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/3 "2021-09-14T09:36:21Z")
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: ```auto 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: ![SensorData6](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/0/03cd6ce34427e1ef611e384e2165fb9528f973c9.png) It looks like that what I want to achieve may be possible to get with Influx. ![SensorData5](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/3/3683094c48327be7c439327cc48e4df24397b96c.png) 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. ---
**Author:** ![Nitesh](https://avatars.discourse-cdn.com/v4/letter/n/8491ac/32.png) [@Nitesh](https://community.influxdata.com/u/Nitesh)\ **Post date:** [September 14, 2021, 12:50pm UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/4 "2021-09-14T12:50:04Z")
@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. ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [September 14, 2021, 3:04pm UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/5 "2021-09-14T15:04:31Z")
Hello @Nitesh, I’m sorry I didn’t think of this earlier. You might also be interested in using: > **[spread() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/universe/spread/)** > > spread() returns the difference between the minimum and maximum values in a specified column. you can give condition with map: > **[Query using conditional logic in Flux | InfluxDB Cloud Documentation](https://docs.influxdata.com/influxdb/cloud/query-data/flux/conditional-logic/#conditionally-transform-column-values-with-map)** > > This guide describes how to use Flux conditional expressions, such as if, else, and then, to query and transform data. Flux evaluates statements from left to right and stops evaluating once a condition matches. You can do this work in a task, execute it on a schedule, and write the output to a new measurement. > **[Process data with InfluxDB tasks | InfluxDB Cloud Documentation](https://docs.influxdata.com/influxdb/cloud/process-data/)** > > InfluxDB’s task engine runs scheduled Flux tasks that process and analyze data. This collection of articles provides information about creating and managing InfluxDB tasks. 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. ```auto |> 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! ---
**Author:** ![Nitesh](https://avatars.discourse-cdn.com/v4/letter/n/8491ac/32.png) [@Nitesh](https://community.influxdata.com/u/Nitesh)\ **Post date:** [September 14, 2021, 3:55pm UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/6 "2021-09-14T15:55:10Z")
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 !! ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [September 14, 2021, 4:29pm UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/7 "2021-09-14T16:29:24Z")
@Nitesh absolutely! I encourage you to share the Flux that works for you so other community members can benefit from your questions and work 🙂 Thank you! ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [September 15, 2021, 4:16pm UTC](https://community.influxdata.com/t/need-to-build-a-query/21663/8 "2021-09-15T16:16:31Z")
Hello @Nitesh, I used aggregateWindow() and spread() to > give me the maximum and minimum from the patten and find the diffrence between the two. ```auto 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") ``` > **[spread() function | Flux Documentation](https://docs.influxdata.com/flux/v0/stdlib/universe/spread/)** > > spread() returns the difference between the minimum and maximum values in a specified column. > **[aggregateWindow() function | Flux Documentation](https://docs.influxdata.com/flux/v0/stdlib/universe/aggregatewindow/)** > > aggregateWindow() downsamples data by grouping data into fixed windows of time and applying an aggregate or selector function to each window.