Currency conversion qlik sense

[Bharathi09]

Hi,

I am developing an application it has field name called as cost currency it has currencies of USD,MXN,CAD

All these should be converted to USD only 

Help me to solve this 

Thanks

[Andrei_Cusnir]

Hello,

I assume that you have something like this:

LOAD
    "Cost" as "Cost Original",
   "Cost Currency" as "Cost Currency"
FROM [...]
(...);

In that use case scenario you can use:

LOAD
   Num("Cost"/PICK(Match([Cost Currency], 'USD', 'MXN', 'CAD'), 1, 20.68140, 1.30320), '#,##0.00;-#,##0.00') as "Cost",
   'USD' as "Cost Currency"
FROM [...]
(...);

As you can see it has converted all the Cost Currency to appear as USD and then converted the values as per the exchange rate supplied. Match() function will return 1, 2 or 3 value based on the currency and the Pick() function will return 1, 20.68, 1.30 etc. Therefore if Match() returns 1 the Pick() function will return 1. So Cost/1 = Cost which means that the value will not change for all USD currency values. However, if e.g. Match() returns 2, then Pick() will return 20.68, therefore Cost/20.68 will give you the cost value of MXN in USD.

NOTE: Keep in mind that you will have to manually supply the real exchange rate whenever you are going to reload the application and if you have more than 2 currencies that needs to be converted to USD, you will have to find a different approach supplying them. Maybe this article [1] will help you achieve you with further details.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

---

[1] https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-use-currency-exchange-rates-in-Qlik-Sense/td-...

[Bharathi09]

Hi I got new more fields in my app now and for each date there is given exchange rate of converting one Currency to other
In that case how Can I convert
Please Help!!

Thanks

[Andrei_Cusnir]

Hello,

Unfortunately your use case scenario is a bit too complicated so it might not be fully supported by Qlik Sense. You can try the following steps:

1. Create a EXCEL file with Date, Cost Currency, Cost Currency Exchange Rate
2. First load the dataset that you have
3. Iterate though each value in the dataset
4. Load the EXCEL file and add a filter to match the current checking date with the current currency
5. if data is loaded, use the exchange rate to calculate the new cost value
6. If data is not loaded then it means that you don't have the exchange rate for that currency on that date
7. Delete temp table
8. Move to next value in the dataset

Additionally:

1.  If you found the exchange rate in step 5, then replace the cost value with the new calculated one and the cost currency with 'USD', however if you haven't found it, then use the current cost value as it is and in cost currency save the string 'NOT FOUND'. This will help you identify any missing exchange rates from your dataset and ensure that you don't have any wrong cost values saved in your new loaded dataset.

I hope that this information is helpful

[Bharathi09]

Hi I have now Currency conversion code as USD,CAD,MXN
I also have Exchange rates for jan1st 2022 to May 2022
Can anyone please help me now to convert currencies to USD

Please help me out


Thank you