# Joinaggregate and argmax() using transform

**URL:** https://discuss.elastic.co/t/joinaggregate-and-argmax-using-transform/254853
**Category:** Kibana
**Tags:** vega
**Created:** [November 10, 2020, 6:49am UTC](https://discuss.elastic.co/t/joinaggregate-and-argmax-using-transform/254853 "2020-11-10T06:49:05Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![smruthip](https://avatars.discourse-cdn.com/v4/letter/s/c5a1d2/32.png) [@smruthip](https://discuss.elastic.co/u/smruthip)
#### Post date: [November 10, 2020, 6:49am UTC](https://discuss.elastic.co/t/joinaggregate-and-argmax-using-transform/254853/1 "2020-11-10T06:49:05Z")

</div>

Hi,

I am trying to display the count of container for each state

example:

container\_id, container\_state, timestamp  
1, loaded, 2pm  
**1, received, 4pm**

here the count of container\_id 1 should be only for received state because container is no longer in loaded state

**2, received, 5pm**  
**3, loaded , 2pm**  
4, received, 2pm  
**4, loaded , 5pm**  
**5, loaded, 3pm**

container\_state count  
Loaded 3  
Received 2

so total there are 5 containers out of which 3 are currently in loaded state and 2 are in received state.

I am trying to use vega-lite and using transform, if I get the most recent actionTime for each container\_id, how do I join this to the container state and display count of all the container\_id per current state of the container.

```auto
{
  "$schema": "https://vega.github.io/schema/vega-lite/v2.json",
  "title": "Total volume per state of container",
  "data": {"url": {"index": "my_index12_2020-11-08"}},
  "transform": [
    {
      "aggregate": [
        {"op": "argmax", "field": "actionTime", "as": "argmax_actionTime"}
      ],
      "groupby": ["currDetails_container_id"]
    }
  ],
  "mark": "bar",
  "encoding": {
    "x": {"field": "argmax_actionTime['currDetails_container_id']", "type": "quantitative"},
    "y": {"field": "currDetails_state", "type": "nominal"}
  }
}

```

---

<div class="post-metadata">

### Author: ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)
#### Post date: [November 10, 2020, 3:43pm UTC](https://discuss.elastic.co/t/joinaggregate-and-argmax-using-transform/254853/2 "2020-11-10T15:43:12Z")

</div>

I see that you've asked a similar question already. Let's move discussion to that thread. [Using rank and partition in vega-lite](https://discuss.elastic.co/t/using-rank-and-partition-in-vega-lite/254831)

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 8, 2020, 3:43pm UTC](https://discuss.elastic.co/t/joinaggregate-and-argmax-using-transform/254853/3 "2020-12-08T15:43:21Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.