Creating a new tag from queries and calculation

[bmenard]

Here’s an example so far of what i want to achieve:

//Return Water Temp
RWT = from(bucket: “Bacnet_Network”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “048_STEM”)
|> filter(fn: (r) => r[“sensor”] == “470500_AI_1103_STEM_LVL0_CWRT”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
|> yield(name: “RWT”)

//Supply Water Temp
SWT = from(bucket: “Bacnet_Network”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “048_STEM”)
|> filter(fn: (r) => r[“system”] == “470000_STEM_ROUTER_PANEL”)
|> filter(fn: (r) => r[“unit”] == “°C”)
|> filter(fn: (r) => r[“sensor”] == “470000_AV_75_STEM_PWP_CHIL_SWT_XFER_AV”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
|> yield(name: “SWT”)

//Chilled Flow
Flow = from(bucket: “Bacnet_Network”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “048_STEM”)
|> filter(fn: (r) => r[“system”] == “470500_STEM_HYD_BSMT”)
|> filter(fn: (r) => r[“unit”] == “°C” or r[“unit”] == “g/min”)
|> filter(fn: (r) => r[“sensor”] == “470500_AI_1104_STEM_CWR_FM”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every:1h, fn: mean, createEmpty: false)
|> yield(name: “Flow”)

//Need to create new tag and timestamp in database with this calculation
//New Value = (RWT - SWT) * 1.8 * Flow * 500.0 / 1000.0

I look at this solution but i can’t make it work:
Create new column to store calculation of two fields from different buckets.

Any help would be appreciated thx!

[Anaisdg]

Hello @bmenard,
I would recommend doing the following:

from(bucket: “Bacnet_Network”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “048_STEM”)
|> filter(fn: (r) => r[“system”] == “470500_STEM_HYD_BSMT”)
|> filter(fn: (r) => r[“sensor”] == “470500_AI_1104_STEM_CWR_FM” or r[“sensor”] == “470000_AV_75_STEM_PWP_CHIL_SWT_XFER_AV” or r[“sensor”] ==“470500_AI_1103_STEM_LVL0_CWRT”)
|> filter(fn: (r) => r["_field"] == “value”)
|> aggregateWindow(every:1h, fn: mean, createEmpty: false)
|> pivot(rowKey:["_time"], columnKey: ["sensor"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: (r["“470500_AI_1103_STEM_LVL0_CWRT"] -  r["“470000_AV_75_STEM_PWP_CHIL_SWT_XFER_AV”])  * 1.8 * r[“470500_AI_1104_STEM_CWR_FM”] *500.0/1000.0}))
|> to(bucket:"new bucket")
|> yield(name: “Flow”)

Are hoping to write that value back to the same database?
If so I’d also use the keep() function to just keep the new calculated value, measurement, and time column.

I made some assumptions about your data so it’s possible that this isn’t a perfect fit. Let me know if you run into any problems.

[bmenard]

Sadly, it returned a Null value. I post the before pivot and after with new calculated value csv file if it can help you saving me.

Thx for helping me.

Before Pivot:

Result:

[simon38]

I think the problem is that “system” is part of the group key, and therefore the results are grouped by “system” and “unit”. I think you need to change the group key by using the group function before pivoting, maybe simply calling group() to eliminate all keys.

[bmenard]

@Anaisdg @simon38 Thx to both of you it solved my problem!

[Anaisdg]

Of course @bmenard! Thank you for sharing your solution.