Currency conversion based on exchange rates

[Bharathi09]

Hi I have columns like Date, Exchange_rate, Cost, Cost_Currency

Cost_Currency has MXN ,CAD,USD currencies . MXN and CAD has to be converted to USD.

Each date has exchange rate

Moreover Exchange_rate is in one table section  and other columns are in other table section.

How can I convert Cost conversion of MXN and CAD to USD by using exchange rates and cost for each date.

Help me with expression that will be helpful for my conversion

Please help me

Thanks in advance!!

[Or]

Sum(Cost * Exchange_rate) would be typical, though the answer may differ depending on your data structure.

It seems you keep asking this question but you're not happy with the answers, so perhaps you could be more clear about what exactly your issue is and provide a sample data set and expected result?

https://community.qlik.com/t5/App-Development/Currency-conversion-qlik-sense/m-p/1954542#M78725

https://community.qlik.com/t5/New-to-Qlik-Sense/Currency-Conversion/m-p/1951291#M209195

[Bharathi09]

Yeah, I will clearly mention what the problem is....
Actually I have to convert 2 currencies MXN and CAD to USD and it has some exchange rates for that has given.
In one table of name 'CURRENCY_TABLE' I have MOVE_DATE ,Cost, Cost_Currency
In other table of name 'CurrencyData' I have From_date, To_date and I did Interval match
1st table is main table. By using 2nd table exchange rate column I have to do currency conversion.
In 1st table I have only dates for 2022 jan to may.
In 2nd table i have dates from 2012 .So I did interval match to match dates between two tables

Inner Join
IntervalMatch(MOVE_DATE_)
Load
FROM_DATE,TO_DATE
Resident CurrencyData;

I matched dates ,mean dates issue got resolved.
Now I want to convert currencies to USD using exchange rate and cost based on MOVE_DATE

[Or]

If you've already matched the dates and the tables are joined on MOVE_DATE, with each MOVE_DATE, and presumably also the TO and FROM currencies, you shouldn't need anymore more than Cost * Rate. If you haven't joined the tables in this manner, you would probably want to do so. If your table is actually one line with fields such as e.g. CAD_TO_USD, MXN_TO_USD etc, you would just write simple if() statements to pick out the correct field, such as Sum(Cost * if(Cost_Currency = 'CAD',CAD_TO_USD'if(Cost_Currency='MXN',MXN_TO_USD,1))).

[Bharathi09]

But I should use exchange rate also here right? to convert to USD
please help

[Or]

I'm sorry, I don't understand your question past what I already suggested. Since your specific data structure isn't clear, I did my best to answer that question for multiple options, but if none of them covers your use case then I'm afraid I'd need more information. 

[Bharathi09]

Thanks alot for your previous solution
Can you please give me a way for getting currenccy conversion by using exchange rates
The exchange rates column is in 2nd table firstly i want that column in 1st table after that I want to do currency conversion using exchange rates

Thanks again

[Or]

I think I already did. You would join the tables as I suggested above and then multiply the cost by the rate.

Here's a rough example:

Load Line, Amount, Currency as FromCurrency, 'USD' as ToCurrency, Date#(Date) as Date INLINE [
Line, Amount, Currency, Date
1, 10, USD, 01/01/2022
2, 15, MXN, 02/02/2022
3, 20, CAD, 03/03/2022];

Left Keep

Load Date#(Date) as Date, FromCurrency, ToCurrency, Rate INLINE [
Date, FromCurrency, ToCurrency, Rate
01/01/2022, MXN, USD, 2.5
01/01/2022, CAD, USD, 1.2
02/02/2022, MXN, USD, 2.6
02/02/2022, CAD, USD, 1.1
03/03/2022, MXN, USD, 2.7
03/03/2022, CAD, USD, 0.8

];

[Bharathi09]

Sure. thanks a lot for the help