Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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.
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?
thy this:
LET varCurrency = num(peek('USDRate',0,'Currency'));
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?
Hi.
Try the following:
LET vCurrency = fieldValue('Currency.USDRate', 1);
Regards.
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);
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