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

[Anonymous]

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

[marcus_sommer · Accepted solution]

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

[maxgro]

add some trace for the 2 variables

what's the result of?

trace varCurrency=$(varCurrency);

trace testvariable=$(testvariable);

[Anonymous]

Hi Both,

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

[marcus_sommer]

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

- Marcus