Flux - Create a table with a measurement while another measurement have a specific value

Fluxlang
1

I want to perform a query that returns the values of a measurement while another measurement have a specific value.
For instance:
- While the value of the following query is 7
QUERY 1 = filter (measurement = variables) (filter 1 = 1) (filter 2 = “stm”)
- Return the values of the following query
QUERY 2 = filter (measurement = variables) (filter 1 = 1) (filter 2 = “tiempo/emergencia”)
-Therefore, I can perform a calculation of the new table.
I have researched a bit and I have found that I can use join to perfrom a similar query but it will only return the rows where the _time is identical, instead I want
the query to return all the values of QUERY 2 while the result the resul of QUERY 1 has a value 7 (“stm” = 7)

dataStream1 = from(bucket: “iot”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “variables”)
|> filter(fn: (r) => r[“entity”] == “1”)
|> filter(fn: (r) => r[“var_id”] == “stm”)
|> filter(fn: (r) => r._value == 7)

dataStream2 = from(bucket: “iot”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “variables”)
|> filter(fn: (r) => r[“entity”] == “1”)
|> filter(fn: (r) => r[“var_id”] == “tiempo/emergencia”)

join(
tables: {d1:dataStream1, d2:dataStream2},
on: [“_time”]
)

Is there any way of returning the values of a table (dataStream2) While the value of another table (dataSteram1) have a specific

A more visual explanation might help understand my goal. DataStream1 change the stage less often than DataStream2 as you can see in the images attached. My goal is to generate a table with all the values of dataStream2 while dataStream1 is 7. As you can see in the joned table, i only get the row that matches exactly the _time column but I want to get all values from 2021-08-06T06:09:33.14Z to 2021-08-06T06:11:33.168Z. Link bellow.


2

Resulting join table. Not good as it only returns 1 row instead of 5 (in the example images)

3

Hello @AlvaroSanchezDomingo,
I have a feeling that what you want to use instead is the from findRecord() and set() instead:

Something like:

dataStream1 = from(bucket: “iot”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “variables”)
|> filter(fn: (r) => r[“entity”] == “1”)
|> filter(fn: (r) => r[“var_id”] == “stm”)
// use idx:0 to pick the row from the table
|> findRecord(fn: true, idx: 1)

// or |> filter(fn: (r) => r._value == 7) and and then use idk:0 if you're only returning one row in that table

dataStream2 = from(bucket: “iot”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “variables”)
|> filter(fn: (r) => r[“entity”] == “1”)
|> filter(fn: (r) => r[“var_id”] == “tiempo/emergencia”)
|> set(key: "dataStream1", value: dataStream1._value)

Or if you’re just setting a known value and that value is an int, string, or float, then you can just use the set() function. I don’t see why you need to grab the value from the original table if you’re defining it explicitly.

4

Hello Anais,

Thanks for replying.

What I need is to get the value of a record (measurement == “variables” with filters entity = “1” and var_id == “tiempo/emergencia”) when the value of other record (measurement == “variables” with filters entity = “1” and var_id == “stm”) is 7 (for example). Thats why I thought i could use a join function which kind of works but it does not return all the raws, it returns only the rows where the timestamp is identical.

Do you see any way around this?

Thanks