how can set a variable with a query value?

[#1]

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.

[Anonymous]

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

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

[maxgro]

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)

[#4]

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.

[JonnyPoole · Accepted solution]

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' ) ;

[maxgro]

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 

[#7]

Thanks a lot Experts.

[Shrikant]

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