# Data table show actuals if exist, otherwise predictions

**URL:** <https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116>\
**Category:** Kibana\
**Created:** [February 26, 2020, 10:02pm UTC](https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116 "2020-02-26T22:02:17Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![craigh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/craigh/32/63376_2.png) [@craigh](https://discuss.elastic.co/u/craigh)\
**Post date:** [February 26, 2020, 10:02pm UTC](https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116/1 "2020-02-26T22:02:17Z")

</div>

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  
},

---

<div class="post-metadata">

**Author:** ![lukeelmers](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukeelmers/32/35230_2.png) [@lukeelmers](https://discuss.elastic.co/u/lukeelmers)\
**Post date:** [March 24, 2020, 7:46pm UTC](https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116/2 "2020-03-24T19:46:07Z")

</div>

Hi @craigh,

You shoud be able to do this by creating a [scripted field](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html), and building your data table visualization with this field. Something along these lines would probably do what you are describing:

```auto
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](https://www.elastic.co/blog/using-painless-kibana-scripted-fields) which has some helpful examples.

---

<div class="post-metadata">

**Author:** ![craigh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/craigh/32/63376_2.png) [@craigh](https://discuss.elastic.co/u/craigh)\
**Post date:** [March 25, 2020, 3:40pm UTC](https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116/3 "2020-03-25T15:40:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![craigh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/craigh/32/63376_2.png) [@craigh](https://discuss.elastic.co/u/craigh)\
**Post date:** [March 25, 2020, 3:51pm UTC](https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116/4 "2020-03-25T15:51:52Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![lukeelmers](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukeelmers/32/35230_2.png) [@lukeelmers](https://discuss.elastic.co/u/lukeelmers)\
**Post date:** [March 25, 2020, 4:48pm UTC](https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116/5 "2020-03-25T16:48:37Z")

</div>

> 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:

```auto
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](https://www.elastic.co/guide/en/elasticsearch/reference/current/null-value.html) 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).

---

<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:** [April 22, 2020, 4:48pm UTC](https://discuss.elastic.co/t/data-table-show-actuals-if-exist-otherwise-predictions/221116/6 "2020-04-22T16:48:41Z")

</div>

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