Currency Converting

[Anonymous]

Hello guys,

sorry if the topic has already been discussed previously, but after 2 weeks of searching, I give up and ask

What would be the most efficient way to covert a number with the current currency exchange rate?

I have 2 data sources:

* Table1 (static data)

Name; NumberEUR

i.e.:

John; 1000

Daniel; 2000

* WebFile1 (daily update)

CurrencyCode; CurrencyRate

i.e.:

CZK; 1.25

USD; 1.65

MNT; 0.45

I am struggling to create a chart/table:

Name; NumberCZK; NumberUSD; NumberMNT

i.e.:

John; 1250; 1650; 450

Sounds simple, in Excel would be already created

But for me as a newbie, there is no obvious link to join the tables, as each table contains different data...

Thanks for help

[vunguyenq89]

Load Currency as a mapping table and use ApplyMap function to lookup exchange rate in Table1. For example:

Currency:

Mapping LOAD * INLINE [

CurrencyCode, CurrencyRate

CZK, 1.25

USD, 1.65

MNT, 0.45

];

Table1:

Load *,

  NumberEUR * ApplyMap('Currency', 'CZK') as NumberCZK,

  NumberEUR * ApplyMap('Currency', 'USD') as NumberUSD,

  NumberEUR * ApplyMap('Currency', 'MNT') as NumberMNT;

LOAD * INLINE [

Name, NumberEUR

John, 1000

Daniel, 2000

];

You can also use Lookup function as an alternative.

If you want to populate NumberXXX automatically according table Currency, you can use a Loop on each entry of Currency, each loop contains a Resident Load.

[vamsee]

Hi Dan,

One of the easy way to achieve this is load the WebFile as a mapping table and applymap() the exchange rates.

Like

Fx_Rates:
Mapping
LOAD * INLINE [
Currency, Rate
USD, 1.16
INR, 83.15
CAD, 1.53
];



Source:
LOAD
Name,
Value /** In Euro **/
,ApplyMap('Fx_Rates', 'USD',1) * Value as NumberUSD
,ApplyMap('Fx_Rates', 'CAD',1) * Value as NumberCAD
,ApplyMap('Fx_Rates', 'INR',1) * Value as NumberINR
;
LOAD * INLINE [
Name, Value
John, 1000
Dan, 2000
];

-----------------------------------

It depends on your requirement but I would not suggest you creating multiple columns for currency change.

Instead keep both the tables with contains Sales currency and the exchange rate for your currencies. Also for EUR, which rate is 1.

Then create in your chart a formula like sum(value * [exchange rate]) as [value USD].

Refer: Multicurrency

[Anonymous]

Thanks, looks like a way to go. Few more questions to this:

a) I write the script in the "Data Load Editor" (I work with Qlik Sense Cloud), right?

b) it seems like You already include the values in the [ brackets ] - I need them to load "fresh" from the WebFile1 everytime, so the data is not static (or why do You write the "CZK, 1.25, ...." numbers there?)

Again, I'm fresh and with almost zero programming experience, so thanks for leading me through this.

[Anonymous]

Hello, and writing a similar reply, if You don't mind:

Thanks, looks like a way to go. Few more questions to this:

a) I write the script in the "Data Load Editor" (I work with Qlik Sense Cloud), right?

b) it seems like You already include the values in the [ brackets ] - I need them to load "fresh" from the WebFile1 everytime, so the data is not static (or why do You write the "CZK, 1.25, ...." numbers there?)

c) thanks for the extra line on how to include the result in the final table - it will surely help, once I manage to get the "online" data into the Fx_Rates

Again, I'm fresh and with almost zero programming experience, so thanks for leading me through this.

[ahmar811]

Try with joins like below

Customer:

Load * inline [

Name, NumberEUR

John, 1000

Daniel, 2000

];

join(Customer)

CurrencyRate:

Load * inline [

CurrencyCode, CurrencyRate

CZK,1.25

USD, 1.65

MNT, 0.45

];

FinalCurrency:

Load

Name,

if(CurrencyCode='CZK',NumberEUR*CurrencyRate) as NumberCZK,

if(CurrencyCode='USD',NumberEUR*CurrencyRate) as NumberUSD,

if(CurrencyCode='MNT',NumberEUR*CurrencyRate) as NumberMNT

Resident Customer;

Drop Table Customer;

I hope this will help you.

Regards

Ahmar

[vunguyenq89]

Hi Daniel,

a) Yes this piece of reload script should be written in Data Load Editor

b) The INLINE load statement is only a quick way to prepare a dataset for demonstration. Of course in the real implementation, you need to replace it with a Load statement that loads data from your actual data source (for example loading from a Webfile)

[vamsee]

The values added in  [ ] (inline Table) are just to provide a sample.

The actual syntax would be 

Fx_Rates:
Mapping LOAD
CurrencyCode,
CurrencyRate
From * WebFile1;

Source:
LOAD
Name,
Value /** In Euro **/
,ApplyMap('Fx_Rates', 'USD',1) * Value as NumberUSD
,ApplyMap('Fx_Rates', 'CAD',1) * Value as NumberCAD
,ApplyMap('Fx_Rates', 'INR',1) * Value as NumberINR
From Source;