Split data with same tag values into different tables

Let me explain my case

Lets assume, I have data with 2 tags and 1 field lets say name, age and score

Now i have 2 different scores for the same group key value which comes in the same table

Now i want to split them into separate tables to view these scores separately

How should i introduce a tag to say that we have two scores (create a new column to indicate the first and second score) and split them into separate tables

Please help me.

@jeyakumar You can add a row index column with a default value of 1 and then use cumulativeSum() to increment the index of each row in the table. For example, lets say you have the following table:

name age score
John 30 100
John 30 120
John 30 135

You would use map() to add a column that acts as a row index. In this example, I’ll just call the row index column, num:

data
    |> map(fn: (r) => ({r with num: 1}))

This produces the following table:

name age score num
John 30 100 1
John 30 120 1
John 30 135 1

Now use cumulativeSum() to cumulatively add values in the num column, essentially incrementing the row index:

data
    |> map(fn: (r) => ({r with num: 1}))
    |> cumulativeSum(columns: ["num"])

This produces:

name age score num
John 30 100 1
John 30 120 2
John 30 135 3

Then if you add num to the group key, you’ll get a separate table for each distinct value of num:

data
    |> map(fn: (r) => ({r with num: 1}))
    |> cumulativeSum(columns: ["num"])
    |> group(columns: ["name", "age", "num"])

Which gives you:

name age score num
John 30 100 1
name age score num
John 30 120 2
name age score num
John 30 135 3

Hopefully this helps.

1 Like

Thanks, This helps a lot.

My case is a little more complex

lets say my data looks like

name age score table
Jacob 30 80 0
John 30 100 1
John 30 100 1
John 30 135 1
John 30 135 1
Scott 20 100 2

and i want to aggregate the scores of each group key values based on timestamp
The rows 2-5 has the same group key values but, row 2 and 3 has a timestamp which is different from rows 4 and 5

like

name age score table
Jacob 30 80 0
John 30 100 1
John 30 135 2
Scott 20 100 3

_time is not part of the group key, and if i try to add that to group() the hours and minutes are different and splits each and every row

How can we do something like this?