Trying to convert data to USD with exchange rates

[Anonymous]

I am in the process of trying to convert my data with USD Average Rate.

Currently I have 3 tables that connect. These are what each excel sheet contains

1. Entity Code and Amount - Amount

2. Exchange rate and Entity Code - Entity

3. Date and Exchange rate. - Currency

I am not sure what function to use, but I want to convert the amount into USD through the data load editor. Currently right now my data shows the amounts in functional currency. Those currencies range from various currencies. I need to convert the numbers on excel sheet 1 to USD. I would like some feedback on how to convert the amounts. In the sample files I am using CAD and need to get it to USD.

Thanks!

Julia

[Lisa_P]

Hi Julia,

As you load the data, you need to ensure that each table is associated correctly.  I have loaded your data into Qlik Sense and this is my data model.

Once this relationship is there you can do a simple calculation Sum(AMOUNT)/[USD Average Rate] as in this table to get the USD amounts.

[jheasley]

If the Data Model is Going to grow into multiple time periods, you will need to do a link by currency and period. 

Currency&'-'&RatePeriod as ConversionID on the Conversion Table, then join the other 2 tables and create the same conversion ID.  you'll get the same effect as before with the simple calculation, but this will allow for more time periods to stack up and for the rate to change with over time.

[Anonymous]

I am following the new field of ConversionID but I don't completely understand linking the other 2 tables together to create the ConversionID again. The only reason I am not following is because the Entity file and the amount file only have 1 field in common. This app will continue to grow into multiple periods so I think this is the solution I am looking for.

[jheasley]

No Worries, its tough to explain, but if you give me a few minutes, i can put a sample app together that shows it all assembled.  The main reason to join them is that the linking field that you already created has to exist in any table that you want to use for linkage/conversion.

[jheasley]

Here you go! - youll have to connect your data to it, but youll get the idea when you look at the load script.