3 Replies Latest reply on Mar 3, 2014 6:11 AM by Tom W

    Calculation on blended data - currency conversion table

    Maciek La

      Hi Everybody!

      I have been struggling with one of my boss request lately. I would like to create a dynamic currency calculations in my Viz. Let's consider data structure as below:

       

      Transactions table

      Transaction IDTransaction currencytransaction valueTransaction date
      1USD102013-01-01
      2EUR202013-01-01
      3GBP302013-01-02
      4USD402013-01-04

       

      rate table

      IDsource currencydestination currencyrate
      1USDEUR0.2
      2EUREUR1
      3GBPEUR0.4
      4USDEUR0.5

       

      Transaction table is supplied from AWS Redhisft and rate table is a csv file (to be migrated to some DB solution later on).

      I would like to receive transaction table calculated in EUR. So I've created a relationship between transaction currency and source currency. I will be only changing the transactions into EUR so no need to do any other custom fields/checks. I've created a calculated field containing: [transaction_table].[transaction_value]*[rate_table].[rate] but obviously it's not the way it should be done as it produces error..

       

      How should I proceed to accomplish such task?

      thanks for help in advance

       

      br,

      maciek.