topic Re: Retrieve Single Value from Data Island for Expression Calculation in QlikView

Retrieve Single Value from Data Island for Expression Calculation

Fri, 12 Jul 2013 10:27:34 GMT

Hi! I have a data island containing two fields: Date and Exchange Rate. I need to retrieve the exchange rate for a specific date, which is entered by the user into an input field in my Qlikview file. I store this date value as a variable.

The goal is to translate the Amount on my normal Data table with the Exchange Rate of the selected Date.

Re: Retrieve Single Value from Data Island for Expression Calculation

swuehl — Fri, 12 Jul 2013 10:36:05 GMT

Try

only({<Date = {'$(vSelectedDate)'} >} [Exchange Rate])

edit: I think it would be best to create another variable vRate

vRate = only({<Date = {'$(vSelectedDate)'} >} [Exchange Rate])

Then use this variable in your expressions, like

=sum(Amount* vRate)

Re: Retrieve Single Value from Data Island for Expression Calculation

er_mohit — Fri, 12 Jul 2013 10:46:56 GMT

Try this

sum({<Date = {"$(=vSelectedDate)"} >} [Exchange Rate])

Re: Retrieve Single Value from Data Island for Expression Calculation

Mon, 15 Jul 2013 10:55:53 GMT

That worked - thanks a lot! I also realized one more mistake on my end - the variable date format and the date format of my data island was not the same. After I applied the same date format, things started to fall in place.

I will also adopt the suggestion of having a second variable for the Exchange Rate Value itself - will make things a lot easier.

Thanks again - also for the fast answer.

Re: Retrieve Single Value from Data Island for Expression Calculation

Mon, 15 Jul 2013 10:55:59 GMT

I will also adopt the suggestion of having a second variable for the Exchange Rate Value itself - will make things a lot easier.

Thanks again - also for the fast answer.

Re: Retrieve Single Value from Data Island for Expression Calculation

Mon, 15 Jul 2013 10:56:11 GMT

I will also adopt the suggestion of having a second variable for the Exchange Rate Value itself - will make things a lot easier.

Thanks again - also for the fast answer.

Re: Retrieve Single Value from Data Island for Expression Calculation

Mon, 15 Jul 2013 10:56:58 GMT

This solution also worked - thanks a lot!

Re: Retrieve Single Value from Data Island for Expression Calculation

Mon, 15 Jul 2013 10:57:22 GMT

This solution also worked - thanks a lot!

Re: Retrieve Single Value from Data Island for Expression Calculation

Mon, 15 Jul 2013 10:58:28 GMT

I will also adopt the suggestion of having a second variable for the Exchange Rate Value itself - will make things a lot easier.

Thanks again - also for the fast answer.

Re: Retrieve Single Value from Data Island for Expression Calculation

Mon, 15 Jul 2013 10:59:04 GMT

This solution also worked – thank you very much for your kind help!