# Last Valid Value of a Field in Scripted Fields

**URL:** <https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918>\
**Category:** Elasticsearch\
**Created:** [October 5, 2020, 4:12am UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918 "2020-10-05T04:12:23Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nastooh](https://avatars.discourse-cdn.com/v4/letter/n/77aa72/32.png) [@Nastooh](https://discuss.elastic.co/u/Nastooh)\
**Post date:** [October 5, 2020, 4:12am UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918/1 "2020-10-05T04:12:23Z")

</div>

Hi

I need to perform math functions on fields with different timestamps. For example, current value of field x, qualified by its size and the last valid value of field y:

```auto
if(doc['x'].size()>0){

 doc['x'].value+doc['y'].LAST_VALID_VALUE; 

}

```

Note that x and y have different timestamps and `doc['y'].size()` will not be \> 0, when `doc['x'].size()` is \> 0.  
Any thoughts?  
Cheers,

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [October 5, 2020, 6:53am UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918/2 "2020-10-05T06:53:14Z")

</div>

Are `x` and `y` part of the same document or are they stored as individual docs? You say, they have different timestamp, which makes me think you talk about individual docs.

I think it will be easier for me and others if you can provide an example with input doc(s) and the expected output.

---

<div class="post-metadata">

**Author:** ![Nastooh](https://avatars.discourse-cdn.com/v4/letter/n/77aa72/32.png) [@Nastooh](https://discuss.elastic.co/u/Nastooh)\
**Post date:** [October 5, 2020, 4:10pm UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918/3 "2020-10-05T16:10:41Z")

</div>

Thank you for your reply. They are part of the same index; however, different document, i.e., were received through 2 different jsons, e.g., `{"x":vlaue_1, ...}` and `{"y":value_2,...}`.

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [October 5, 2020, 6:32pm UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918/4 "2020-10-05T18:32:35Z")

</div>

In this case you won't be able to solve your use case with a scripted field, a scripted field can not work over 2 documents, but only within the same document. With other words, a scripted field would only work if `x` and `y` are part of the _same_ document.

In order to combine docs you need aggregations. Do the docs you want to combine share a field with the same value, e.g. an id? In this case you would group the docs using that id (e.g. with a terms aggregation) and use another aggregation to combine `x` and `y`.

For large data sets, you need a composite aggregation, if you aim for persisting the result in an index again - as a new document - you can use a transform.

---

<div class="post-metadata">

**Author:** ![Nastooh](https://avatars.discourse-cdn.com/v4/letter/n/77aa72/32.png) [@Nastooh](https://discuss.elastic.co/u/Nastooh)\
**Post date:** [October 5, 2020, 6:52pm UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918/5 "2020-10-05T18:52:06Z")

</div>

Thank you for your prompt reply. Documents do share a common unique field, would you be able to point to an example, for aggragation templating purposes?  
Cheers,

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [October 5, 2020, 7:23pm UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918/6 "2020-10-05T19:23:33Z")

</div>

You can check this [discuss post](https://discuss.elastic.co/t/merging-documents-based-on-matched-fields-values/249728), this is about merging 2 documents. Conceptually you want the same but in addition you want to apply a calculation. In a nutshell you need to get the values for `x` and `y` in the map phase and do the calculation in the reduce phase.

If you haven't worked with aggregations before: [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html)

If you want to create new documents, checkout transform: [https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html)

I know this is a lot to read. It might be easier to look for blogs first, e.g. [https://www.elastic.co/blog/intro-to-aggregations/](https://www.elastic.co/blog/intro-to-aggregations/) or this webinar recording about transform: [https://www.elastic.co/webinars/introducing-data-frame-transforms-for-elastic-machine-learning](https://www.elastic.co/webinars/introducing-data-frame-transforms-for-elastic-machine-learning)

---

<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:** [November 2, 2020, 7:23pm UTC](https://discuss.elastic.co/t/last-valid-value-of-a-field-in-scripted-fields/250918/7 "2020-11-02T19:23:45Z")

</div>

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