Perform mathematical functions from two index in Kibana

[ibras (Ibrahim)]

Hello,
I am trying to perform a multiplication functions between two fields in different index in Kibana.

My first index looks like :

My first index :

  {
           "_index": "first_index",
           "_type": "type_1",
           "_id": "c7086175a8d312b47c62949f635f0c9164325ede973b09b9c3905ad7824bcc6d",
           "_score": 1.0,
           "_source": {
             "amount": 0.43616541,
             "date": "2020-10-12T20:28:05.000Z"
           }
         }

My seconde index

   {
            "_index": "second_index",
            "_type": "type_2",
            "_id": "id",
            "_score": 1.0,
            "_source": {
              "rate": 0.43616541,
              "date": "2020-10-12T20:28:05.000Z"
            }
          }

I created in Kibana an index pattern who regourps both indexes but i don"t know how to use both field rate and amount, to create a visualization that contains the amount with specified rate.

the multiplication should be based on date.

Thanks

[AClerk]

Not sure it is possible.
AFAIK You need both fields in the same doc.

[mastersmit (Smit)]

Is there any link between two except the date? If thats the case, will there be only two entry per day?

Also, if both the data is coming via the logstash, you can use agregate of logstash to combine the data related via a unqiue link(id)

[ibras (Ibrahim)]

Nope, the only link between both index is The date, also data is coming from different storm bolts in different nodes.

[flash1293 (Joe Reuter)]

If the date is always a perfect match, you could use a transform (https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html) to "join" both indices together. You would end up with a single document containing both rate and amount.

For time based data it's also possible to set up a continuously running transform job.

[ibras (Ibrahim)]

Thanks a lot @flash1293, that works perfect with some combination with scripted fields and saved search i could have the proper visualations I needed