Data table show actuals if exist, otherwise predictions

[craigh (Craig S Hamilton)]

Hi,

I have results for fantasy players (hockey). I generate predictions for the upcoming week, and then as the week passes the actual results are also entered into a team-results index.

There is a score_type field which is 'p' for projections, and then 'a' for actuals.

How could I get the data table to show actuals for the date if they exist, otherwise show the projections ?

Here is example of an entry.

thanks,
craig

{

"_index": "fantasy-bot-team-results",
"_type": "_doc",
"_id": "5639.2020-03-01.p",
"_version": 2,
"_score": 0,
"_source": {
"G": 0.39999999999999997,
"A": 0.3666666666666667,
"+/-": 0.5,
"PIM": 0.16666666666666666,
"SOG": 2.3333333333333335,
"FW": 0.09999999999999999,
"HIT": 1.0666666666666667,
"selected_position": "RW",
"score_type": "p",
"play_date": "2020-03-01",
"name": "Reilly Smith",
"week_number": 20,
"fantasy_team_id": "2",
"player_id": 5639
},

[lukeelmers]

Hi @craigh,

You shoud be able to do this by creating a scripted field, and building your data table visualization with this field. Something along these lines would probably do what you are describing:

doc['score_type.keyword'].value == 'p' ? doc['predicted_store.keyword'].value : doc['actual_score.keyword'].value

(I made up the field names here because I can't quite tell which of the scores in your doc are predicted vs actual).

For more on scripted fields, you may want to check out this blog post which has some helpful examples.

[craigh (Craig S Hamilton)]

@lukeelmers thanks! I will give that a try, and read the blog post.

[craigh (Craig S Hamilton)]

@lukeelmers actually I see a problem with your suggestion.

projected scores and actual scores are 2 different documents in my index.

ie id: "_id": "5639.2020-03-01.p"

for actual the id changes from p to a at end.

so: id: "_id": "5639.2020-03-01.a" would denote when I have actual results.

I suppose I could instead create a doc which has projected values when those are available - with empty actuals, and then update it when actuals come in. The your scripted field suggestion would work great.

Do you think that would be a better solution for this? I quite new at using Elasticsearch so don't feel like I have a good grasp on how to structure the data.

thanks again.
Craig

[lukeelmers]

Do you think that would be a better solution for this?

Our favorite answer to questions like this is "it depends"

Does the time that the actual scores are entered matter to you for any reason? I'm assuming the answer is "no" since you don't have a timestamp stored in your document.

If that's the case, it seems to me it would make things much easier for you to combine it all into a single document and do an _update when actual scores are available:

POST scores/_doc/5639.2020-03-01
{
  "projected": 1
}

POST scores/_update/5639.2020-03-01
{
  "actual": 2
}

GET scores/_doc/5639.2020-03-01
{
  "_source": {
    "projected": 1,
    "actual": 2
  }
}

Otherwise it doesn't seem you are getting much benefit from having duplicate documents for each game. If the only thing that's changing is adding the actual score, having a single document per game will make visualizing the data much easier if you are wanting side-by-side comparisons of projected vs actual.

Depending on how you want the missing actual values to be treated between the time you create the projected score and the time the actual is entered, you might also want to consider specifying a null value in your mappings. This would be useful if you want the actual field to be indexed or searched while its value is still null. (This isn't necessary if you don't have a use case for indexing/searching the null fields, however).