Count rows and subtract an int

InfluxDB 2
1

Hi all, I’m a newbie looking for a way to aggregate values coming from a top 20 selection and all the other ones. I still have to study and work on the logic, but I’m encountering an issue while trying to group together all values which are not part of the top 20.

merge = from(bucket: "mybucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  
c = merge
  |> count()

top20 = merge
  |> top(n:20, columns: ["_absvalue"])

bottom = merge
  |> bottom(n: c-20)

union(tables: [top20, bottom])
  |> group()
  |> pivot(columnKey: ["colname"], rowKey: ["_time"], valueColumn: "_value")
  |> yield()

The error I receive is:

type error @11:18-11:20: expected [A] but found int

Can you suggest me a way to cast c to int, so that I can subtract 20?

2

Hello @uniteddolphin,
You’ll need to use the findRecord() function after the count() to get the value. However this approach will only work if you’re expecting to apply this logic for one table.
What does your schema look like?
Are you looking to apply this to all of your measuremets and tags?
If so I’d also apply a group() after the range().

3

Hi @Anaisdg, yes you’re right - I have to group data together to have one single table.
I edited the code in the following way:

merge = from(bucket: "mybucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> group()
  
c = merge
  |> count()
  |> findRecord(
    fn: (key) => key.tag == "_value",
    idx: 0)

top20 = merge
  |> top(n:20, columns: ["_absvalue"])

bottom = merge
  |> bottom(n: c-20)

union(tables: [top20, bottom])
  |> group()
  |> pivot(columnKey: ["colname"], rowKey: ["_time"], valueColumn: "_value")
  |> yield()

After that, I still take the error “int is not Record” in the “bottom” call… probably I still have to extract int value from Record, even if it’s not clear to me how it is structured and I cannot find a way to debug its value. Can you provide me an example?

4

Hello @uniteddolphin,
You have to access the value you want like:

bottom = merge
  |> bottom(n: c._value-20)

Can you please try that?

5

I receive the error “unsupported binary expression invalid - int”.
If I write this instead, I receive the error below.

bottom = merge
  |> bottom(n: c._value)

error calling function "bottom" @40:6-40:25: error calling function "_sortLimit" @universe.flux|290:8-290:55: error calling function "limit" @universe.flux|282:8-282:19: keyword argument "n" should be of kind int, but got invalid