# Flux query generating separate line for each tag

**URL:** <https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382>\
**Category:** Fluxlang\
**Tags:** query, flux\
**Created:** [January 15, 2022, 9:34pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382 "2022-01-15T21:34:39Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![umarth](https://avatars.discourse-cdn.com/v4/letter/u/a183cd/32.png) [@umarth](https://community.influxdata.com/u/umarth)\
**Post date:** [January 15, 2022, 9:34pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/1 "2022-01-15T21:34:39Z")

</div>

Hello there,

I am new to InfluxDB and I hope this isn’t too ignorant a problem ☹ But I’ve spent days on this and I do not know what I am not seeing.

I have generated a dataset )which represents expenses) using the line protocol and I want to create a simple line graph using this dataset, which I want to display using Grafana

Here is a snippet from the dataset I’m importing:

```auto
transaction,source=csv,category=none amount=-134.06,description="example description text" 1610409651297000000
transaction,source=csv,category=food amount=55.08,description="example description text 2" 1610520338723000000
transaction,source=csv,category=none amount=1000,description="example description text 3" 1610541142475000000

```

This is the flux query I am using at the moment

```auto
from(bucket: "expenses")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "transaction")
  |> filter(fn: (r) => r["_field"] == "amount")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

```

My problem is that InfluxDB/Grafana will always create a **separate line for each of the tags**. However, the tags only represent metadata. I only want so see a single line representing all expenses from the dataset.

Is this clear? What am I doing wrong here?

---

<div class="post-metadata">

**Author:** ![eriktar](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/eriktar/32/8744_2.png) [@eriktar](https://community.influxdata.com/u/eriktar)\
**Post date:** [January 17, 2022, 9:41am UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/2 "2022-01-17T09:41:28Z")

</div>

Influx is a time series database. Meaning that timestamps are important. Looking at the sample data set, all three have unique timestamps. So pr default they will end up on separate lines. You need to either make sure they have the same timestamp when inserting into the database. Or you need to modify the time stamps (large `every` in `aggregateWindow`) for example.

When you have matching timestamps you should be able to use `pivot()` to “flatten” the data.

> **[pivot() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/universe/pivot/)**
>
> pivot() collects unique values stored vertically (column-wise) and aligns them horizontally (row-wise) into logical sets.

```auto
  |> pivot(
    rowKey:["_time"],
    columnKey: ["_field"],
    valueColumn: "_value"
  )

```

---

<div class="post-metadata">

**Author:** ![umarth](https://avatars.discourse-cdn.com/v4/letter/u/a183cd/32.png) [@umarth](https://community.influxdata.com/u/umarth)\
**Post date:** [January 17, 2022, 10:19am UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/3 "2022-01-17T10:19:56Z")

</div>

Thank you so much for responding!

I have a question, though: Since Influx is a time series database, why should the timestamps be unique? Each datapoint should be on a _different_ timestamp, I would assume.

In my scenario every datapoint represents an expense which naturally has a timestamp. Or do I misunderstand the timestamps in line protocol?

> [@eriktar](#):
>
> Or you need to modify the time stamps (large `every` in `aggregateWindow` ) for example

What do you mean by “large `every`”?

---

<div class="post-metadata">

**Author:** ![eriktar](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/eriktar/32/8744_2.png) [@eriktar](https://community.influxdata.com/u/eriktar)\
**Post date:** [January 17, 2022, 11:50am UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/4 "2022-01-17T11:50:32Z")

</div>

From you original post, my understanding is that you want a single line?

> [@umarth](#):
>
> I only want so see a single line representing all expenses from the dataset.

To accomplish this you need to have same time for all the points on the same line when viewing - provided you want the output to be a time series as well. The timestamp is the key influx uses to figure out which of the datapoints belong together.

If the timestamps on ingestion to the database are different for all items, you need another time for influx to collect the data on. Hence the `every` in `aggregateWindow` needs to be “large”. Large here being relative to the data. Do you want an aggregated line pr day? Pr hour? Pr week? That depends on the use case. If your use case is for a restaurant - perhaps pr hour would make sense? For a household, perhaps pr day would make more sense?

I also notice that you use `mean`as the aggregate function. Also depending on use case. For a restaurant perhaps `mean` makes sense to see how the spending patterns is distributed for lunch vs dinner customers. For a family budget perhaps `sum` is more interesting.

---

<div class="post-metadata">

**Author:** ![umarth](https://avatars.discourse-cdn.com/v4/letter/u/a183cd/32.png) [@umarth](https://community.influxdata.com/u/umarth)\
**Post date:** [January 17, 2022, 1:35pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/5 "2022-01-17T13:35:42Z")

</div>

I think I need to explain myself better.

My original intent was to generate a line chart with `cumulativeSum`, because my data contains expenses as well as income.

This is the query I am now using

```auto
from(bucket: "expenses")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "transaction")
  |> filter(fn: (r) => r["_field"] == "amount")
  |> filter(fn: (r) => r["category"] == "none" or r["category"] == "food")
  |> cumulativeSum()

```

And this is the time series chart that is generated from that (within the \*Explore" tab of InfluxDB)

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/a/aec103e6947314c8d8d783792d6117dfa02080d0.png)

This is generally correct, only there are two separate charts for each of the `category` tabs (food/none). I want InfluxDB to completely ignore that there is a `category` tag at all. It is not relevant for this graph.

---

<div class="post-metadata">

**Author:** ![eriktar](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/eriktar/32/8744_2.png) [@eriktar](https://community.influxdata.com/u/eriktar)\
**Post date:** [January 17, 2022, 1:52pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/6 "2022-01-17T13:52:10Z")

</div>

Ah! Sorry for being a bit dense. 🙂 I assumed you meant line as in log line. Of course it’s graph line in this context. 🙂

I think you get what you want by dropping the category column like this

```auto
from(bucket: "expenses")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "transaction")
  |> filter(fn: (r) => r["_field"] == "amount")
  |> filter(fn: (r) => r["category"] == "none" or r["category"] == "food")
  |> drop(columns: ["category"])
  |> cumulativeSum()

```

---

<div class="post-metadata">

**Author:** ![umarth](https://avatars.discourse-cdn.com/v4/letter/u/a183cd/32.png) [@umarth](https://community.influxdata.com/u/umarth)\
**Post date:** [January 17, 2022, 2:06pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/7 "2022-01-17T14:06:40Z")

</div>

> [@eriktar](#):
>
> ```auto
> |> drop(columns: ["category"])
> 
> ```

Thank you!  
This feels like the right solution, but somehow I end up with this. It looks like one line alright, but still not quite what I was expecting

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/5/526ffd37cd7afc8b5a8261f344304f931d6a9e22.png)

---

<div class="post-metadata">

**Author:** ![eriktar](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/eriktar/32/8744_2.png) [@eriktar](https://community.influxdata.com/u/eriktar)\
**Post date:** [January 17, 2022, 2:15pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/8 "2022-01-17T14:15:22Z")

</div>

Guessing that the values are in two tables still. Perhaps sort will do the trick? Or perhaps you also need to group the data?

```auto
from(bucket: "expenses")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "transaction")
  |> filter(fn: (r) => r["_field"] == "amount")
  |> filter(fn: (r) => r["category"] == "none" or r["category"] == "food")
  |> drop(columns: ["category"])
  |> group()
  |> sort(columns: ["_time"])
  |> cumulativeSum()

```

---

<div class="post-metadata">

**Author:** ![umarth](https://avatars.discourse-cdn.com/v4/letter/u/a183cd/32.png) [@umarth](https://community.influxdata.com/u/umarth)\
**Post date:** [January 17, 2022, 2:52pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/9 "2022-01-17T14:52:38Z")

</div>

Yes! Grouping and sorting the data by time did do the trick!

Thank you so much for your help! I really appreciate it!

---

<div class="post-metadata">

**Author:** ![eriktar](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/eriktar/32/8744_2.png) [@eriktar](https://community.influxdata.com/u/eriktar)\
**Post date:** [January 17, 2022, 2:59pm UTC](https://community.influxdata.com/t/flux-query-generating-separate-line-for-each-tag/23382/10 "2022-01-17T14:59:51Z")

</div>

Your welcome. 🙂 BTW: If you mark my previous post as “solution” it will be simpler for other people searching for similar issues to find it.