Table view with multiple fields from multiple measurements/tables with where clause

InfluxDB 2
, ,
1

I created a table view in grafana where am using fields from multiple measurements of same database. But I need to use a where clause in the query. The following is my query to get a single field using the where clause & this works. But how can I use the similar format for multiple tables in same table view??

from(bucket: “L_OEEA_COMPACT”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “RT_Dashboard”)
|> filter(fn: (r) => r["_field"] == “StationID” or r["_field"] == “Performance”)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: “_value”)
|> map(fn: (r) => ({ r with _value: r.Performance }))
|> filter(fn: (r) => r.StationID == 2)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

2

Hello @Aritra666B,
You shouldn’t have to pivot() and then map() those two functions are essentially undoing each other.
Instead I’d try something like:

data = from(bucket: “L_OEEA_COMPACT”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “RT_Dashboard”)
|> filter(fn: (r) => r["_field"] == “StationID” or r["_field"] == “Performance”)

two = data 
|> filter(fn: (r) => r.StationID == 2)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "2") 

three = data
|> filter(fn: (r) => r.StationID == 3)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "3")

I’m making assumptions about what your data looks like here so please let me know if this isn’t helpful.

3

Hi,
Actually I need to combine the output of fields from multiple tables in a single table view, but there is always the filter option.
1.
from(bucket: “L_OEEA_COMPACT”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “RT_Dashboard”)
|> filter(fn: (r) => r["_field"] == “StationID” or r["_field"] == “Performance”)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: “_value”)
|> map(fn: (r) => ({ r with value: r.Performance }))
|> filter(fn: (r) => r.StationID == 2)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)
|> drop(fn: (column) => column =~ /^(start|stop)/)
|> keep(columns: ["_value", “_time”])
|> rename(columns: {_value: “Performance”})
2.
from(bucket: “L_OEEA_COMPACT”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “ShiftData”)
|> filter(fn: (r) => r["_field"] == “StationID” or r["_field"] == “TEEP”)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: “_value”)
|> map(fn: (r) => ({ r with value: r.TEEP }))
|> filter(fn: (r) => r.StationID ==2)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)
|> drop(fn: (column) => column =~ /^(start|stop)/)
|> keep(columns: ["_value", “_time”])
|> rename(columns: {_value: “TEEP”})

This works for graphs nicely but not for tables. Is there a way to join them?

4

Hello @Aritra666B,
yes you can use the join() function:

But what I think you really want is to use multiple fields to visualize both series:

and 
|> yield(name: "2”)

You can also consider filtering for everything (different IDs or measurements or whatever):

|> filter(fn: (r) => r["_field"] == “StationID” or r["_field"] == “Performance”)
|> filter(fn: (r) => r.StationID == 1 or r.StationID == 2 or r.StationID == 3  )

And then adding an empty group() function to combine all the tables into one