topic Re:Getting values from db during Runtime and storing it in an variable in QlikView

Re:Getting values from db during Runtime and storing it in an variable

Anonymous — Fri, 13 Nov 2015 06:08:23 GMT

Hi,

I am actually fetching values from an table and storing it in a variable(I use specific arguments here). I am also fetching values from the same table and store it in an different variable for an different argument.What I did here was I used peek function and stored those two values into two different variables using let function. Now what I need to do is I need to insert this variables inside an query which runs from database. But when I use both the variables in the query it is not returning any values.

For instance,

Table:
SELECT test.testtable('IND',9) as currency_IND FROM DUAL;
LET varCurrency = Num(peek('currency_IND',0,'Table'));

DROP Table Table;

Table1:
SELECT test.testtable('USD',9) as currency_USD FROM DUAL;

LET testvariable = Num(peek('currency_USD',0,'Table1'));

DROP Table Table1;

Now what I need to do is,

---this is just an part of the query

AND ( TAR.test_change_id = ('$(varCurrency)')
or TAR.test_change_id = ('$(testvariable)') )

---this is my requirement--- The values should be entered in the query at run time

But it is not happening. Kindly help me here. Please note that if we enter the value of test_change_id directly it is working and also the values of varcurrency and testvariable will always be one line only.

Thanks in advance

Re:Getting values from db during Runtime and storing it in an variable

marcus_sommer — Fri, 13 Nov 2015 09:54:24 GMT

I think the reason for the failure is that the variables couldn't fetch a value from your select-table and return therefore NULL. This will be caused that qv ignored your table-statement, see: Re: SQL alias for tables in QlikView.

The solution is quite easy - use a preceeding load for this, like:

Table:

Load *;
SELECT test.testtable('IND',9) as currency_IND, test.testtable('USD',9) as currency_USD FROM DUAL;
LET varCurrency = Num(peek('currency_IND',0,'Table'));

LET testvariable = Num(peek('currency_USD',0,'Table'));

DROP Table Table;

trace '$(varCurrency)';

If this wasn't only dummy-code you could put both fields into one query. To check various values within the script you could use the trace-statement - it will pop up within the execution window and written within the log-file.

- Marcus

Re:Getting values from db during Runtime and storing it in an variable

maxgro — Fri, 13 Nov 2015 10:04:00 GMT

add some trace for the 2 variables

what's the result of?

trace varCurrency=$(varCurrency);

trace testvariable=$(testvariable);

Re:Getting values from db during Runtime and storing it in an variable

Anonymous — Fri, 27 Nov 2015 10:56:50 GMT

Hi Both,

It is not working. Actually trace is not fetching any values when running in debug mode also. is there any other way?

Re:Getting values from db during Runtime and storing it in an variable

marcus_sommer — Fri, 27 Nov 2015 12:57:23 GMT

Comment the drop table out and take a look on the table itself - which fields and values are there?

- Marcus