selecting field value in load script

[#1]

Hi,

I'm loading a table with currency rates into my load script. The table looks similar to this:

currency	rate
EUR	1,0
USD	1,1
CNY	6,5

Now I would like to use those loaded rates as variables still in my load script. So how can I select with a expression exactly the rate for the currency "USD"? sth. like "SET vRateUSD = currency(USD)" or so  .... But how is the correct syntax or is it even possible?

Thanks fore reply!!

[pokassov]

Hi!

t1:

load  rate

resident currencyrate

where currency='USD';

vUSD=peek('rate',0, 't1');

drop table t1;

[siddheshmane]

Hi,

Try using this..

LET varx = $(USD)  and so on..

[#4]

This is what you need.

Check the attached file.

Mohssine AMHACHI

[#5]

this is not the solution I am looking for ...

[#6]

this doesn't make sense to me at all ...

[#7]

thanks for your reply! you got me on the track. I work now with the "lookup" expression so I can select exactly the value I need. But NOW(!) I need to add an additional column, so I have:

year          currency          rate

2015          EUR               1,0

2016          EUR               1,2

So if I use "LET vCurrency = lookup('currency', 'rate', 'EUR')" it automatically selects the FIRST value (1,0) so is it possible to use some operatiors like AND?

[#8]

hi

SET Rate USD = currency(USD)

[antose]

Hi!

To fetch one value into a variable you can do like this.

But can't you skip this and just use Sum(amount)*Rate in the client?

[mrossoit · Accepted solution]

Hi Enrico,

if you have to choose the value with the highest year you could order the load of your table descending for the field year.

For a better precision you should create another field as the concatenation of year and currency (year & currency as yyyy_curr) and then use it in lookup function:

LET vCurrency = lookup('yyyy_curr', 'rate', '2016EUR')

Hope it helps

MR