topic FOREACH in expressions in QlikView

FOREACH in expressions

Anonymous — Mon, 06 Feb 2012 13:42:52 GMT

Hi everybody,

i have this table

IDDEVICE IDFIELD ENERGY

a 1 50

a 1 40

b 1 70

b 1 60

c 1 100

c 1 110

d 2 100

d 2 10

e 2 205

e 2 200

f 3 10

g 4 15

Does exist some kind of FOREACH that i can use like this:

if ( IDFIELD = 1,

FOREACH IDDEVICE take max(ENERGY)

if ( IDFIELD = 2,

FOREACH IDDEVICE take max(ENERGY)

.....

FOREACH in expressions

CELAMBARASAN — Mon, 06 Feb 2012 13:49:50 GMT

Hi,

Did you expect this in script expression?

If so then you can do this by using group by like

Load

IDFIELD,

Max(ENERGY)

Resident

TableName

GROUP BY IDFIELD;

Hope it helps

Celambarasan

FOREACH in expressions

swuehl — Mon, 06 Feb 2012 13:52:43 GMT

What do you do with the list of max(Energy) values then? Sum them up?

Could you give an example of your expected outcome?

Maybe you could use something like

=sum( {<IDFIELD={1}>} aggr(max( {<IDFIELD={1}>} ENERGY), IDDEVICE))

FOREACH in expressions

Anonymous — Mon, 06 Feb 2012 14:22:00 GMT

SWUEHL

Yes i want the SUM and your expression works good.

In the same way, can i group by IDFIELD too ?

FOREACH in expressions

swuehl — Mon, 06 Feb 2012 14:34:30 GMT

Probably yes,

(I am not 100% sure what you are looking for, again, describing your expected outcome might help here):

try

=sum( aggr(max( ENERGY), IDFIELD, IDDEVICE))

FOREACH in expressions

Anonymous — Mon, 06 Feb 2012 16:47:51 GMT

SWUEHL

one last question:

Now i want to take the Min value of energy but only where ENERGY > 0

=sum( aggr(MIN( ENERGY), IDFIELD, IDDEVICE))

i'm trying to put IF(ENERGY>0,......) everywhere in the expression but seems not working...

thanks

FOREACH in expressions

swuehl — Mon, 06 Feb 2012 17:00:57 GMT

Try a set expression

=sum({<ENERGY = {">0"}>} aggr(MIN({<ENERGY = {">0"}>} ENERGY), IDFIELD, IDDEVICE))