Hello,

I’m trying to (eventually) calculate a percentage using the following query :

```
from(bucket: "kgnv_rd")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "${DeviceID}" and (r._field == "Totals_Id" or r._field == "Totals_Value" ))
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> filter(fn: (r) => r.Totals_Id == 63 or r.Totals_Id == 64 )
|> map(fn: (r) => ({r with _totalwork: if r.Totals_Id == 63 or r.Totals_Id == 98 then r.Totals_Value/1000.0 else 0.0}))
|> map(fn: (r) => ({r with _totaltransport: if r.Totals_Id == 64 or r.Totals_Id == 98 then r.Totals_Value/1000.0 else 0.0}))
//|> map(fn: (r) => ({r with _percentagework: r._totaltransport+r._totalwork}))
|> drop(columns: ["Totals_Value"])
```

This will result in the following output ;

What I need is to calculate the percentage between _totaltransport and _totalwork, which are based upon the identifier Totals_id ( 63 / 64 ). Actually the values from 63/64 need to be in the same row to be able to calculate, but i don’t now how I can allign them properly?

I’ve been searching/trying for hours but i’am unable to solve this unfortunately.

Is there anybody who can help me in the right directions?

Thanks,

Hi @bart1992 ,
Welcome to the community. Are you happy to export a snippet of your data? Hopefully, I can then help you out from there.

Thanks

scott
November 22, 2021, 5:20pm
3
@bart1992 I’ve had to make some assumptions about your data based on your query, but I think this will work for you:

```
data = from(bucket: "kgnv_rd")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "${DeviceID}" and (r._field == "Totals_Id" or r._field == "Totals_Value" ))
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
totalTransport = data
|> filter(fn: (r) => r.Totals_Id == "64")
|> map(fn: (r) => ({r with _field: "_totaltransport", _value: r.Totals_Value/1000.0 }))
totalWork = data
|> filter(fn: (r) => r.Totals_Id == "63")
|> map(fn: (r) => ({r with _field: "_totalwork", _value: r.Totals_Value/1000.0 }))
union(tables: [totalTransport, totalWork])
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: r._totaltransport / r._totalwork }))
```

@scott @Jay_Clifford many thanks for your responses.

Managed to get it working based upon your example @Jay_Clifford

```
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "${DeviceID}" and (r._field == "Totals_Id" or r._field == "Totals_Value" ))
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
totalTransport = data
|> filter(fn: (r) => r.Totals_Id == 64)
|> map(fn: (r) => ({r with _field: "totaltransport", _value: r.Totals_Value/1000.0 }))
totalWork = data
|> filter(fn: (r) => r.Totals_Id == 63)
|> map(fn: (r) => ({r with _field: "totalwork", _value: r.Totals_Value/1000.0 }))
union(tables: [totalTransport, totalWork])
|> truncateTimeColumn(unit: 10m)
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with PercentageWork: (r.totaltransport / r.totalwork) }))
|> map(fn: (r) => ({ r with PercentageTransport: 100.0 - r.PercentageWork }))
|> drop(columns: ["totaltransport", "totalwork"])
|> last(column: "PercentageTransport")
```

Had to use the ‘truncateTimeColumn’ function to be able to align the row’s from Transport/Work since there was a slight ( 10 ms ) time difference between the datapoints ( vehicle CANbus data ).

Again, thanks a lot!

That was all @scott glad you got it working.

1 Like