How to do display amount in different currency after Currency conversion

Elastic Stack Kibana
1

I am newbie to elastic/kibana and i am stuck with a problem.

I have transactional records in which multiple currency types are used like
doc - 1
{
Amount : 500
Currency : USD
}
doc -2
{
Amount : 100
Currency : CAD
}

In Kibana, i want to display the sum of the amount in USD.

I thought about adding a new index with the currency-exchange rate mapping and then join it with transaction index as we do in RDBMS, but it is not possible in elastic.

What is the best possible way to do this ?

Thank you for your help

2

The easiest way might be to make a scripted field that converts to USD. It would probably look something like this:

if (doc['Currency.keyword'].value.equals('ABC')) {
  return doc['Amount'].value * 1.5;
} else if (doc['Currency.keyword'].equals('DEF')) {
  return doc['Amount'].value * 20;
} else {
  return doc['duration'].value;
}
3

Thanks lukas for the reply.
i thought about that.
But by doing that, i have to write a big script to convert all types of currencies; and updating the conversion rate periodically would be a challenge.Although this is the easiest solution, this might not be a suitable approach in long run.

Is storing another field with [AmountInUSD] from application side the best solution for this problem?

4

Yes, you could do that too. If you do that, then the amount would be fixed, which may or may not be what you want. If you want the amounts to always reflect the amount in USD at the time of purchase rather than the current time, then it would be the better solution.

5

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