Is there a smart way to multiply by a given currencyrate?

[andy]

Hi folks,

First of all this code has to work on Qlikview 7, by that time there was no input-field in Qlikview.

I have a databasetable with transactions and they are labeled with different currencies like this

amount currency

71 SEK

23 NOK

54 NOK

....

The problem is to convert all of them to the same currency, either SEK or NOK in this example.

The currency rate shall be given in a Excelfile.perhaps like this

NOKToSEKRate SEKToNOKRate

1,26 0,79

I understand that I can solve the problem by adding an expression with an if-statement in every sheetobject and calculate the correct amount during runtime but I would better like a solution which takes the calculation overhead during script-execution time and produces maybe one column named amount_SEK and another named amount_NOK.

One solution could be if the rates could be assigned into a variable with the SET - statement but I cannot find a way to read the number 1,26 or 0.79 into a variable from the xls-file.

Another way I thought of would be to join the two tables and then while reloading from that new resident an IF-statement could be used to multiply with the rates but unfortunately the joined resident is just never created by Qlikview - I don't see why.

What would be a nice solution according to you guys?

Here is my try with two small example xls-files

/Andreas

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/5710.trasactions.xls:550:0]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/3644.rates.xls:550:0]

RATES:
LOAD NOKToSEKRate,
SEKToNOKRate
FROM
rates.xls
(biff, embedded labels, table is Sheet1$);


TRANS:
LOAD amount,
currency
// IF(currency='NOK',amount*NOKToSEKRate,amount) as amount_SEK does not work
FROM
trasactions.xls
(biff, embedded labels, table is Sheet1$);


TEST1_tmp: //this resident is never created by Qlikview....by some reason?!?
LOAD *
RESIDENT RATES;
JOIN
LOAD *
RESIDENT TRANS;


//TEST1: //so this cannot be used
//LOAD
// amount,
// currency,
// IF(currency='NOK',amount*NOKToSEKRate,amount) as amount_SEK,
// IF(currency='SEK',amount*SEKToNOKRate,amount) as amount_NOK
//RESIDENT TEST1_tmp;
//DROP TABLE TEST1_tmp

[lmonincx]

hi,

I think the issue of the join is that you join on *, try to list the fields with the first field for the join and see if that table is being created. In case you create a tabel wich exactly has the same columns as an earlier table loaded, it's beeing appended to the first table so not creating the second table seperate, you can avoid this by adding an extra column like '1' as counter and delete this field later on in the script.

I think i would try the if statement.

Hope this solves your issue.

Regards, Linda