Ho to sum multiple lines with different currency into one line

[#1]

Dear Expert,

I have following requirement(see more details in attachment): I have a source with 3 lines in different currency and I want to sum them and convert them into one line with a single currency(See Final table). I haven't figured out a easy and nice way to do it.

Any suggestion is greatly appreciated!

Weidong

Source
	C1	C2	C3	K1	K2	K3	K4	K5
EUR	10	0	20	50	0	50	30	10
USD	0	30	0	0	60	0	20	40
GBP	10	0	10	20	30	10	10	10
ExchangeRate			Rate
USD	EUR		0.90599
EUR	USD		1.103765
USD	GBP		0.70815
GBP	USD		1.41213
EUR	GBP		0.781631
GBP	EUR		1.279376
Final
	C1	C2	C3	K1	K2	K3	K4	K5
EUR	22.79376	27.1797	32.79376	75.58752	92.74068	62.79376	60.91356	59.03336	Sum of line 3 to 5 in EUR
USD	25.15895	30	36.1966	83.43085	102.3639	69.30955	67.23425	65.15895	Sum of line 3 to 5 in USD
GBP	17.81631	21.2445	25.63262	59.08156	72.489	49.08156	47.61193	46.14231	Sum of line 3 to 5 in GBP

[Oleg_Troyansky]

I'm assuming that you want to do it in the load script... I'd suggest the following multi-step process:

1. Create a distinct list of "To Currencies" (EUR, USD, and GBP), and join it to the original table - this will be a Cartesian join, so the number of rows will get trippled.

2. Load the table of Currency Rates as a MAPPING table, where the first field is the concatenation of the two currencies and the second field is the rate, like this:

     USD-EUR     0.90599

3. Reload the transactional table one more time, applying the Map to the combinations of the "From currency"
and "Two currency" in order to get the corresponding rate:

LOAD

     ID,

     OriginalAmount * ApplyMap('CurrencyMap', Currency & '-' & ToCurrency, 0) as ConvertedAmount

...

4. Finally, aggregate the result by the field ToCurrency:

LOAD

     ID,

     ToCurrency as Currency,

     sum(ConvertedAmount) as Amount

resident

     Temp

GROUP BY

     ID,

     ToCurrency

;

This process should do what you are looking for.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

[#3]

Hi, make the currencies cross join to source table and multiplay with Rate values

please check the below script:

SOURCE:

LOAD * INLINE [

Curreny,C1,C2,K1 ,K2,K3 ,K4,K5

EUR,10,0,50,0,50,30,10

USD,0,30,0,60,0,20,40

GBP,10,0,20,30,10,10,10

];

Join (SOURCE)

LOAD * Inline [

TargetCurreny

EUR

USD

GBP

];

Join(SOURCE)

LOAD

  Target AS Curreny ,

  Source AS TargetCurreny ,

  Rate

INLINE [

Target,Source,Rate

USD,EUR,0.90599

EUR,USD,1.10376494221791

USD ,GBP ,0.70815

GBP,USD,1.41213019840429

EUR,GBP ,0.7816311

GBP,EUR,1.279375910196

];

FINAL:

LOAD

  TargetCurreny AS Curreny,

  SUM(C1*Alt(Rate,1)) AS C1,

  SUM(C2*Alt(Rate,1)) AS C2,

  SUM(K1*Alt(Rate,1)) AS K1,

  SUM(K2*Alt(Rate,1)) AS K2,

  SUM(K3*Alt(Rate,1)) AS K3,

  SUM(K4*Alt(Rate,1)) AS K4,

  SUM(K5*Alt(Rate,1)) AS K5

Resident SOURCE

Group By TargetCurreny

;

DROP Table SOURCE;

Also attached the qvw for reference.

[#4]

Oleg, we have to multiply with 1 for same Currencies.

OriginalAmount * ApplyMap('CurrencyMap', Currency & '-' & ToCurrency, 1) as ConvertedAmount