Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gnmq
Contributor III
Contributor III

Deriving a Column

Hi,

 

I have two tables.

Table A: Transaction Data

Table B: Money Rates

 

Table A:

Date, Product, CurrCode, Amount

20190101, Product A, THB, 500

Table B:

Date, CurrFrom, CurrTo, Rate

20190101, THB, USD, 31

 

I will like to derive a new field in Table A with the $$ converted from THB to USD...

May I know how to implement this? i.e., 500/31 = USD $16.13 

P.s. asking because I have over 80+ unique CurrCode in data. Hence, seeking an easier way to approach this.

Labels (1)
2 Replies
lorenzoconforti
Specialist II
Specialist II

You can join the two tables together to have the Rate in the first table, do the calculation and then drop the table

Not sure if you want to convert from a single currency (THB) or to a single currency (USD). In the script below, I'm converting different input currencies to USD

You could also do it with the lookup function but join is faster; you need to be careful though that TableB has single entries for the new key "CurrKey" otherwise data will be duplicated in your database; to prevent this I've included a Group By so that only one combination of Date and CurrFrom will be included in the join

 


TableA_temp:
Load *, (Date & CurrCode) as CurrKey;
Load * inline [
Date, Product, CurrCode, Amount
20190101, Product A, THB, 500
20190201, Product B, THB, 350
20190201, Product B, EUR, 12
];

TableB:
Load *, (Date & CurrFrom) as CurrKey;
Load * inline [
Date, CurrFrom, CurrTo, Rate
20190101, THB, USD, 31
20190201, THB, USD, 30.5
20190201, EUR, USD, 1.10
];


left join (TableA_temp) load CurrKey, FirstValue(Rate) as Rate resident TableB Group by CurrKey;

TableA:
Load *, (Amount/Rate) as ConvertedAmount Resident TableA_temp;

drop table TableA_temp;
drop field Rate From TableA;
drop field CurrKey;

NitinK7
Specialist
Specialist

Hi 

try following it my be helpful to you.

TableA:
Load * inline [
Date, Product, CurrCode, Amount
20190101, Product A, THB, 500
20190201, Product B, THB, 350
20190201, Product B, EUR, 12
];

left join

TableB:
Load * inline [
Date, CurrFrom, CurrTo, Rate
20190101, THB, USD, 31
20190201, THB, USD, 30.5
20190201, EUR, USD, 1.10
];

Final:
LOAD *,
'$' & ' ' & Num(Amount/Rate,'#,##0.00') as USD
Resident TableA;
DROP Table TableA;

currency.PNG

Regards,

Nitin.