topic min and aggr question [sample included] in QlikView

min and aggr question [sample included]

amien — Wed, 10 Feb 2010 00:44:49 GMT

see attachment

aggr(min(if(due > 0,due)),code)

why doesnt this work?

i need there the code-value from the corresponding min(if(due > 0,due).

Gives me all '-' cause there are many answers ... while there only should be one

min and aggr question [sample included]

Wed, 10 Feb 2010 03:19:11 GMT

min and aggr question [sample included]

amien — Wed, 10 Feb 2010 11:01:04 GMT

empty message scar .. did i miss anything?

min and aggr question [sample included]

amien — Thu, 11 Feb 2010 11:34:11 GMT

bump

Re. :min and aggr question [sample included]

fseregaza — Thu, 11 Feb 2010 11:51:33 GMT

Hi Amien,

That's not that it doesn't work. In fact, the '-' character here means that you have several values. Try the following expression and you'll see that I'm right.

= Concat( Aggr( Min( If( due > 0, due ) ), code ), ', ' )

Here, I just concatenated the list returned by your expression.

I hope that'll answer your question.

Best regards,

Franck SEREGAZA

Business & Decision

Re. :min and aggr question [sample included]

amien — Thu, 11 Feb 2010 14:00:28 GMT

"Gives me all '-' cause there are many answers ... while there only should be one"

but then i have the wrong expression

cause i need the code (and that is only one) where min(if(due > 0,due)) is the lowest .. based on filenumber and value

so .. where min(if(due > 0,due)) is the lowest (within the same filenumber) .. i need that code .. and that can only be one

see attachment ..

on filenumber 1 .. and value = 200, due = 4 is the lowest

on filenumber 1... and value = 300 , due = 5 is the lowest

so, i need this output:

1, 200, 4, 002

1, 300, 5, 002

1, 500, 4, 004

1, 100, 4 005

2, 100, 28, 202

Re. :Re: Re. :min and aggr question [sample included]

fseregaza — Thu, 11 Feb 2010 14:55:29 GMT

Sorry Amien, I didn't correctly read your post. Try this one:

=concat({$<due={'$(=min(if(due>0, due)))'}>} distinct code, ', ')

It works with your current data, but what if you have more "due" values above 0. Try and tell me.

Best regards,

Franck SEREGAZA

Business & Decision

Re. :Re: Re. :min and aggr question [sample included]

fseregaza — Thu, 11 Feb 2010 15:03:40 GMT

Don't bother. It doesn't work (I just get too tired to see the right column). Sorry.

Best regards,

Franck SEREGAZA

Business & Decision

Re. :Re: Re. :min and aggr question [sample included]

fseregaza — Thu, 11 Feb 2010 15:35:26 GMT

Sorry for keeping you waiting, but I've finally found out the solution to your problem. Use the "FirstSortedValue" function:

=FirstSortedValue(code, if(due>0, due))

I'm pretty sure, but if I'm wrong, keep me informed.

Best regards,

Franck SEREGAZA

Business & Decision