topic Re: how can set a variable with a query value? in QlikView

how can set a variable with a query value?

Tue, 23 Sep 2014 18:48:54 GMT

Hi dear Experts,

I need set a variable with a query value, some thing like set Var=(load max(E) from resident consulta);

any body could help me?

best regards.

Re: how can set a variable with a query value?

Anonymous — Tue, 23 Sep 2014 18:53:29 GMT

Let Var = peek('E', -1, 'consulta')

and make sure you have ordered consulta table by field E (asc) before variable initialization

Re: how can set a variable with a query value?

maxgro — Tue, 23 Sep 2014 19:26:18 GMT

consulta:

load * inline [

year , tipology , val , %

2013 , rmcost , 7000 , rmcost/sales

2013 , pcost , 2000 , pcost/sales

2013 , margin , 1000 , margin/sales

];

set var =

'test:

noconcatenate

load *

resident consulta

where year = 2013;

drop table consulta;

$(var)

Re: how can set a variable with a query value?

Tue, 23 Sep 2014 22:47:49 GMT

Thanks Konstantins ,

Thanks Massimo,

but for example if my field is monetary and I cant ordered by him ?

I need for example a count or max?

best regards.

Re: how can set a variable with a query value?

JonnyPoole — Tue, 23 Sep 2014 23:05:32 GMT

this will load 1 record into the MaxTable. The record will include the maximum value of E from the consulta table. Then it will assign that one (max) value to a variable called vMax using the peek() function

MaxTable:

load max(E) as MaxValue

resident consulta;

let vMax = peek('MaxValue', 0, 'MaxTable' ) ;

Re: how can set a variable with a query value?

maxgro — Wed, 24 Sep 2014 07:59:47 GMT

if you need the max or min or count of a field in a variable copy Jonathan's script and replace max with your aggregation (min, count, ...)

when you use a resident load (as in Jonathan script) you can add an order by

Re: how can set a variable with a query value?

Wed, 24 Sep 2014 14:52:38 GMT

Thanks a lot Experts.

Re: how can set a variable with a query value?

Shrikant — Thu, 11 Aug 2022 10:10:51 GMT

Thank you very much, I spent almost 2 hours on this. Tried different ways mentioned on forum but my variable was getting NULL value