# Unable to replicate top function in flux
**URL:** https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225
**Category:** Fluxlang
**Tags:** flux
**Created:** [August 22, 2022, 4:14pm UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225 "2022-08-22T16:14:38Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@mr\_sharma](https://community.influxdata.com/u/mr_sharma)
#### Post date: [August 22, 2022, 4:14pm UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225/1 "2022-08-22T16:14:38Z")
I’m new to Flux query and have been trying to convert InfluxQL to FluxQL and having lot of issues or inaccurate data. Following query is in Influxql:
```auto
SELECT top("value","name",1) FROM (SELECT non_negative_derivative(last("weighted_io_time"), 1ms) AS "value" FROM "diskio" WHERE ("host" =~ /^$data_nodes_prod$/ AND "name" !~ /[0-9]/) AND $timeFilter GROUP BY time($__interval), "host", "name" fill(null)) GROUP BY "host"
```
For which I could write following fluxql but it does not give me the correct answer. Even I’ve tried with pivot but don’t understand where and how exactly this could be implemented.
```auto
from(bucket: v.bucket)
|> range(start: v.timeRangeStart)
|> filter(fn: (r) => r._measurement == "diskio")
|> filter(fn: (r) => r._field == "weighted_io_time")
|> filter(fn: (r) => r["name"] == "/[0-9]/")
|> filter(fn: (r) => contains(value: r["host"], set: ${data_nodes:json}))
|> derivative(unit: 1ms, nonNegative: true)
|> top(n: 1)
```
Thanks in advance.
---
### Author:  [@Anaisdg](https://community.influxdata.com/u/Anaisdg)
#### Post date: [August 22, 2022, 6:14pm UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225/2 "2022-08-22T18:14:02Z")
Hello @mr_sharma,
Welcome!
Thanks for your question. I would translate this:
```auto
SELECT top("value","name",1) FROM (SELECT non_negative_derivative(last("weighted_io_time"), 1ms) AS "value" FROM "diskio" WHERE ("host" =~ /^$data_nodes_prod$/ AND "name" !~ /[0-9]/) AND $timeFilter GROUP BY time($__interval), "host", "name" fill(null)) GROUP BY "host"
```
If you’re just looking at the top 1 value, I’d use the max() function instead. I’m confused how you’re getting a derivative after just getting the last() value.
I recommend splitting your subquery up incrementally and translating each bit to flux. In other words, try getting successfully filtering for your data first, then applying the last() function successfully, then the derivative, etc.
My Flux query would look like:
```auto
from(bucket: v.bucket)
|> range(start: v.timeRangeStart)
|> filter(fn: (r) => r._measurement == "diskio")
|> filter(fn: (r) => r._field == "weighted_io_time")
|> filter(fn: (r) => r["host"] ==/^$data_nodes_prod$/ and r["name"] == "/[0-9]/")
|> filter(fn: (r) => contains(value: r["host"], set: ${data_nodes:json}))
|> aggregateWindow(every: $__interval, fn: last)
|> derivative(nonNegative: true)
|> max()
```
Does that help?
---
### Author:  [@Anaisdg](https://community.influxdata.com/u/Anaisdg)
#### Post date: [August 22, 2022, 7:24pm UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225/3 "2022-08-22T19:24:31Z")
If that doesn’t help, or trying to go systematically doesn’t work for you, then try breaking your query up into incremental parts.
---
### Author:  [@mr\_sharma](https://community.influxdata.com/u/mr_sharma)
#### Post date: [August 23, 2022, 1:45pm UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225/4 "2022-08-23T13:45:05Z")
Hi @Anaisdg , Thank you for the response. I guess, I’m able to get closer to the answer. I executed following query:
```auto
from(bucket: v.bucket)
|> range(start: v.timeRangeStart)
|> filter(fn: (r) => r._measurement == "diskio")
|> filter(fn: (r) => r._field == "weighted_io_time")
|> filter(fn: (r) => contains(value: r["host"], set: ${data_nodes_prod:json}))
|> filter(fn: (r) => r["name"] == "sdc")
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> derivative(unit: 1ms, nonNegative: true)
|> max()
```
which gives me the correct data but not the correct labels. The labels looks like below but I need to show only two columns i.e. $host:$name above the bars.
Appreciate your help.
---
### Author:  [@Anaisdg](https://community.influxdata.com/u/Anaisdg)
#### Post date: [August 24, 2022, 5:15pm UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225/5 "2022-08-24T17:15:00Z")
Hello @mr_sharma,
Perhaps you can use the keep() function?
> **[keep() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/universe/keep/)**
>
> keep() returns a stream of tables containing only the specified columns.
---
### Author:  [@mr\_sharma](https://community.influxdata.com/u/mr_sharma)
#### Post date: [August 30, 2022, 10:06am UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225/6 "2022-08-30T10:06:17Z")
Hi @Anaisdg , Not sure but keep() function do not seems fit in this case. I’ve tried but its not working. If you look at the labels in the image above I just need to cut out two column names. I’d have done this very easily using sed/awk in linux, but here i’m unsure how to do that.
In older inlfuxdb query it was achieved using “Alias by” in following image:
---
### Author:  [@mr\_sharma](https://community.influxdata.com/u/mr_sharma)
#### Post date: [August 31, 2022, 1:56pm UTC](https://community.influxdata.com/t/unable-to-replicate-top-function-in-flux/26225/7 "2022-08-31T13:56:26Z")
I figured out the solution for desired label for the output. For one of my graph the lable was shown as:
```auto
{_resourcegroup_="rg-influx-enterpise_prod_westeu", partition="12", topic="to_metrics_shared"}
```
which I wanted to show as following:
```auto
rg-influx-enterpise_prod_westeu:to_metrics_shared:12
```
Solution:
Go the Transform tab, search for Rename by Regex.
Match: {_resourcegroup_=“(._)“, partition=”(._)”, topic=“(.\*)”}
Replace: $1:$3:$2
Thanks.