Math with query results

Welcome & Getting Started
,
1

Hello I am trying to do some math with my query results using Flux. Everything works fine until I try to calculate something with the results. I would like to divide divide operational by nfaults:

// Get all the data from the bucket filtered for FAULT_LASER
data = from(bucket: "plcview_4/autogen")
  |> range(start: 2021-01-29T00:00:00.000Z, stop: now()) 				// regular time range filter
  |> filter(fn: (r) => r._measurement == "FAULT_LASER") 				// filter for the measurement
  |> elapsed(unit: 1ms, timeColumn: "_time", columnName: "duration") 	// calculate time difference between rows
  |> yield(name: "data")
// returns data tables for every unique set of tags (workplace and equipmentNumber)

// Filter for all "No-Fault-Values" and sum their durations
operational = data
  |> filter(fn: (r) => r._value == 0) 	// filter for all rows where FAULT_LASER = 0 --> No Faults
  |> group() 							// group all data tables together
  |> sum(column: "duration") 			// sum all the durations from all data tables 
  |> yield(name: "operational")

// Count the number of faults
nfaults = data
  |> filter(fn: (r) => r._value == 1)	// filter for all rows where FAULT_LASER = 1 --> Faults
  |> group()							// group all data tables together
  |> count()							// count the number of records
  |> yield(name: "nfaults")
  
// Calculate MTBF
mtbf = operational / nfaults

Why do my calculations not work?

2

Hello @strittmm,
operational and nfaults are table streams, not individual values so you can’t simply divide them.
You would need to perform a join and then a map().

join(tables: {nfaults: nfaults, operational: operational}, on: ["_time"], method: "inner")
|> map(fn: (r) => ({ _value: r._value_operational / r._value_nfaults }))
3

@Anaisdg thanks, but for some reason this did not work for me…
Instead I did this and it works just how I intended:

mtbf = data
  |> reduce(
  	fn: (r, accumulator) => ({ 
    	operational: r.duration + accumulator.operational,
        nFaults: accumulator.nFaults + 1
    }),
    identity: { operational: 0, nFaults: 0 }
  )
  |> map(fn: (r) => ({ _value: r.operational/ r.nFaults }))
1 Like