[Question] How to get the amount of x field per date from a table

[Gusis93]

Hi, thanks a lot for this awesome package,

Currently I'm trying to do a transform that should be straight forward but I can't figure it out.

I'm trying to count the amount of trackers created in each date, so if it looks like this:

data = [
   {
      date: '2021-02',
      tracker: 'A',
      id: 1
   },
   {
      date: '2021-02',
      tracker: 'B',
      id: 2
   },
   {
      date: '2021-02',
      tracker: 'B',
      id: 3
   },
   {
      date: '2021-03',
      tracker: 'B',
      id: 4
   },
   {
      date: '2021-04',
      tracker: 'A',
      id: 5
   },
]

I want the result to tell me this:

transform = [
  {
      date: '2021-02',
      A: 1,
      B: 2
  },
  {
      date: '2021-03',
      B: 1,
  },
  {
      date: '2021-04',
      A: 1,
  }
]

I tried grouping by date and then getting an array of the tracker values but that gives me kind of what I want but not exactly:

dt
    .groupby('date')
    .rollup({
      trackers: op.array_agg('tracker'),
    })
    .objects()

[Gusis93]

To answer my own question, I read some other discussions and found this

dt
    .groupby('date')
    .pivot('tracker', { value: () => op.sum('1') })

which gives me the exact result I'm looking for, even with better results because it gets the other categories which aren't there and sets a 0.