Can I use result of query as a variable in a second query?

[#1]

I'm pulling in data to handle some currency conversion.

In my script, I first do a query on our Currency table and I return a single result. So if I was displaying this in a table in Qlikview, I would just drop the variable Currency.USDRate. This works fine.

However, I have a second query in my script where I'm pulling together all the sales numbers. What I would like to do in that query is something like this:

Currency.USDRate*Sales as Sales_USD,

This throws an error because the script does not seem to know about the result that was calculated previously at runtime.

Is there a way to take the result of one query and use it in a second query in the same script?

[#2 · Accepted solution]

Hi,

let me know if I undestood....Currency.USDRate its a field with 1 value? in this case you can store that value in a variable

LET varCurrency = peek('Currency.USDRate',0,'Table');

where 'Table' is the name of the table where you have the Currency.USDRate field.

then you just need to do this:

$(varCurrency )*Sales as Sales_USD,

Hope this helps

[#3]

If I understood correctly, the variable is storing one single value

In a general sense, if the results is a set of values which are dynamically varying (an array), then I am afraid it may not work it out this way. I am refering to the situation where the dynamic values rendered by a particular set of filter criteria, are to be used as input variables for another set of calculations. We faced a similar situation.

[#4]

The peek idea seems like it should work, but it does not seem to return any values for me.

Maybe I am using the wrong syntax or something ( I read the Help file but it wasn't very thorough).

The name of the table is Currency and the name of the field is USDRate - it definitely holds just one record.

This is what I have in my script after the currency table is read in: LET varCurrency = peek('USDRate',0,Currency);

This returns NULL. Any suggestions?

[#5]

thy this:

LET varCurrency = num(peek('USDRate',0,'Currency'));

[#6]

No, that gives me the same result - nothing.

I'm really hopeful that this will work somehow - it seems like this is the right track.

Any other ideas?

[spividori]

Hi.

Try the following:

LET vCurrency = fieldValue('Currency.USDRate', 1);

Regards.

[#8]

I figured it out. The code you suggested (without parenthesis around the table is correct) but if you have a Qualify statement before this line it messes up the peek. I moved my qualify statement to below the peek and it worked fine.

So here is the working peek statement:

LET varCurrency = peek('USDRate',0,Currency);

[alwinsch]

Hi,

I just read your solution to this problem, but can you help me, when the output of the first query

is more than 1 result

kind regards,

Alwin