Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

selecting field value in load script

Hi,

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

currencyrate
EUR1,0
USD1,1
CNY6,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!!

1 Solution

Accepted Solutions
mrossoit
Creator II
Creator II

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

View solution in original post

9 Replies
pokassov
Specialist
Specialist

Hi!

t1:

load  rate

resident currencyrate

where currency='USD';

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

drop table t1;

siddheshmane
Creator
Creator

Hi,

Try using this..

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

Not applicable
Author

This is what you need.

Check the attached file.

Mohssine AMHACHI

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

hi

SET Rate USD = currency(USD)

antose
Partner - Creator II
Partner - Creator II

Hi!

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

Skärmklipp.PNG

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

mrossoit
Creator II
Creator II

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