pivoting data with count

Fluxlang
1

looks like i not understand how pivot works

I have follow records:

events,company=...,campaign=...,project=...,event_type=... value=1i

And want to execute SOLR like pivoting by company, campaign, project so output should be like:

company:1
 - count(event_type): 6
 - campaign: 2
    - count(event_type): 3
    - project: 5
      - count(event_type): 2
    - project: 6
      - count(event_type): 1
 - campaign: 4
    - count(event_type): 3
    - project: 7
      - count(event_type): 2
    - project: 8
      - count(event_type): 1

is it possible with flux pivot? seems it works in completely different way

2

Hello @mogadanez,
Welcome! I’m so sorry for the delay. Sometimes questions get lost.
Can you please share some of your input Annotated CSV?

3

The pivot for flux is different from an excel pivot. Pivot in flux acts as a way to join columns from different group keys into a single table.

If you can tell me what you want to do, there may be a way to do that in flux. I am not sure if pivot is the correct function though. It seems like the output you want is to have some nested data and I don’t think this is possible in flux. The output of flux is a stream of tables. But, you can potentially get the values you want from yielding the query results at different points during the query. If you want to count each of the event types per project, you can do this:

from(...)
    |> range(...)
    |> filter(...)
    |> group(columns: ["company", "campaign", "event_type"])
    |> count()
    |> yield(name: "events")
    |> group(columns: ["company", "campaign"])
    |> sum()
    |> yield(name: "project_events")
    |> group(columns: ["company"])
    |> sum()
    |> yield(name: "campaign_events")

You can also potentially write these to different measurements so they are easy to graph. If I can get a better idea of what you want to do, we can find a way to make the output work for you.