topic Re: selecting field value in load script in QlikView

selecting field value in load script

Thu, 02 Apr 2015 12:00:23 GMT

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!!

Re: selecting field value in load script

pokassov — Thu, 02 Apr 2015 12:19:14 GMT

Hi!

t1:

load rate

resident currencyrate

where currency='USD';

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

drop table t1;

Re: selecting field value in load script

siddheshmane — Thu, 02 Apr 2015 12:26:20 GMT

Hi,

Try using this..

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

Re: selecting field value in load script

Thu, 02 Apr 2015 13:20:15 GMT

This is what you need.

Check the attached file.

Mohssine AMHACHI

Re: selecting field value in load script

Thu, 02 Apr 2015 13:55:49 GMT

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

Re: selecting field value in load script

Thu, 02 Apr 2015 13:58:40 GMT

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

Re: selecting field value in load script

Thu, 02 Apr 2015 14:01:42 GMT

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?

Re: selecting field value in load script

Thu, 02 Apr 2015 14:29:29 GMT

SET Rate USD = currency(USD)

Re: selecting field value in load script

antose — Thu, 02 Apr 2015 14:42:50 GMT

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?

Re: selecting field value in load script

mrossoit — Thu, 02 Apr 2015 15:12:31 GMT

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