Unable to visualize data correctly

[Anne_Kim]

Hi there,

I've an array, where elements look like this:

•  t    _id  	          htqe7m0BSOdvtuXlL_IK
  

•   t   _index	      currency_data
  

•   #   _score	       - 
  

•   t   _type	          data
  

•   #   player_id	       457
  

•   #   soft_currency_amount	    95,792
  

•   soft_currency_last_update 	Oct 21, 2019 @ 16:57:01.000
  

This is information about a game player, his currency balance and the date when his balance was updated. There can be several recods for the same player.
I need to visualize (firstly, as a Data Table) the balance of each player at the moment of the LAST update

{
  "aggs": {
    "4": {
      "terms": {
        "field": "player_id",
        "order": {
          "_key": "asc"
        },
        "size": 400
      },
      "aggs": {
        "2": {
          "terms": {
            "field": "soft_currency_amount",
            "order": {
              "2-orderAgg": "desc"
            },
            "size": 1
          },
          "aggs": {
            "3": {
              "terms": {
                "field": "soft_currency_last_update",
                "order": {
                  "1": "desc"
                },
                "size": 1
              },
              "aggs": {
                "1": {
                  "cardinality": {
                    "field": "player_id"
                  }
                }
              }
            },
            "2-orderAgg": {
              "max": {
                "field": "soft_currency_last_update"
              }
            }
          }
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    {
      "field": "soft_currency_last_update",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "soft_currency_last_update": {
              "format": "strict_date_optional_time",
              "gte": "2019-10-11T21:00:00.000Z",
              "lte": "2019-10-12T20:30:00.000Z"
            }
          }
        }
      ],
      "filter": [
        {
          "match_all": {}
        }
      ],
      "should": [],
      "must_not": []
    }
  }

As a result, I've a table with following columns: player_id, soft_currency_amount, soft_currency_last_update and Unique count of player_id. The last metric is used just for fit.
All data are correct, but the "soft_currency_last_update" column doesnt contain the real last update. It shows the date when the appropriate balance amount appeared for the first time.
For example, a player has 1000 coins on the 21 Oct at 2p.m., then his balance counted 1300 coins at 5p.m. The next (and the last) balance update was on the 22 Oct at 8a.m. when the balance remained the same (1300 coins).
In this situation a table record will contain soft_currency_amount - 1300, soft_currency_last_update - 21 Oct 5p.m (instead of 22 Oct at 8a.m.)
What's wrong with it?

[wylie (Wylie Conlon)]

You should be using the Top Hits aggregation, which can return a single record sorted by something like timestamp. This is possible using Visualize

[Anne_Kim]

Thank you!