Newbie question: Lookup tables

  • Grafana version: V9.2.0 on Windows
  • Apology up front - new to Grafana so will likely be using the wrong nomenclature, so please be considerate!
  • I’m looking for a way to use a value pulled from influx, interpolate a result from a table, compare to a second value from influx & give a text output.
  • Specifically I have a fluid pressure & temperature, want to use the pressure to look up the saturation temperature in a table to output if the fluid temperature is above/around/below that.

I have it working nicely in Excel, but would prefer to get a live panel on the go on my dashboard:

  • =IF(E7>Q7,“Above”,IF(E7<Q7,“Below”,“Around”))
    • E7 = Fluid Temp.
    • Q7 = FORECAST($D7,O7:P7,M7:N7) ← Saturation Temp.
      • D7 = Fluid Pressure
      • M7:N7 = Sat. table pressures above & below D7 (done with index matching to the saturation table)
      • O7:P7 = Sat. table temps associated with M7:N7

Welcome,

Is this table in a database and what database type is it in?

1 Like

It’s not anywhere other than excel on my desktop just yet - literally at square one for knowing where to go with this. Did my best to look it up but haven’t struck upon the right terminology to get an answer!

no worries, we can provide you some guidance. So do you currently have influx installed with data in it?

Yes, I can pull the two required data feeds live

since we do not have access to your influx db please post some sample data as csv here like following

date,metric,value,
2022-10-18 08:00:00,pressure,23
2022-10-18 08:00:00,temperature,123

this is just a sample so you will have to fill in the rest as it is in your influxdb.

Also are you using flux queries or influxql?

Time,Pressure,Temperature
1666084810000,7.5,9.56
1666084820000,7.5,9.57
1666084830000,7.6,9.58

I don’t know on flux vs. influxql - is there an easy way to tell?

1 Like

what version of influxdb are you using? what does the query you are currently look like?
Also those timestamps, what precision are they?

Typical query: image

Values are logged every 10 seconds, but as I’m pulling from grafana (rather than influx directly) it tends to skew that in the data I pull, but what I want is a calculation live in Grafana so I’m not bothered by that.

ok so you indeed using flux query language.

Please answer this

Also those timestamps, what precision are they?

Values are logged every 10 seconds

precision means, millisecond, seconds, minutes?

It might be a bit of a tangent, but how does the precision affect what I’m after? I can’t say for certain but it’s in the second to millisecond range. So long as the output I’m after is accurate to within 5 minutes I’m happy (I’m looking at weeks worth of data that changes slowly).

doesn’t affect you at all, but it affects us being able to help you. as I said in the beginning we don’t have access to your data so we need to spin up a copy of your data in our environment.

Almost reassuring it’s not quite as simple as “obviously you do this”!

Working an example with the numbers above, I think the process comes down to:

  1. Storing a table in Grafana
  2. Interpolating values
  3. Running the output logic

Fluid measurements from influx:

T P
7.5°C 9.56 bar

Saturation data (from TBC):

T P
5°C 9.34bar
6°C 9.63bar
7°C 9.93bar
8°C 10.23bar
  • Tsat @ 9.56bar comes out as 5.76°C
  • T = 7.5°C
  • T > Tsat
  • Output = “Above”
1 Like

I think you will have to use a Map to store your fluid pressure & temperature data in a table in InfluxDB.

For the linear interpolation, Influx has that covered too.

How do you envision your data to appear in Grafana? In a simple table of values? Maybe share what your Excel model looks like.

2 Likes

Thanks Grant - it looks like I’ll have to commit and get my head around a little bit of programming!

I hadnt envisaged anything much fancier than a state timeline.

I’ve managed to make some progress, but have hit a snag I can’t get past:

I’ve managed to upload a CSV with the pressure values I’m after, using a field tag for the associated temperatures. This way I’ve successfully pulled the pressure value by looking up the temperature tag - e.g, look up the field tagged “7”, it pulls the pressure “9.93”.

Separately I can pull the live value of the temperature reading “TL1 = 7.5” & round it down to “TL1int = floor(TL1) = 7”.

The hurdle I can’t get past is then using “TL1int” in place of “7” typed manually to pull “9.93”. I’m thinking if this works I’ll then be able to see a time history of the different pressure values with changing temperature readings. I’ve two thoughts on the issue:

  1. I shouldn’t be using field tags like that
  2. I need to convert the “TL1int” from a number to text or other format