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
@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"])
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