Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
amien
Specialist
Specialist

min and aggr question [sample included]

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

8 Replies
Not applicable

amien
Specialist
Specialist
Author

empty message scar .. did i miss anything?

amien
Specialist
Specialist
Author

bump

fseregaza
Partner - Contributor III
Partner - Contributor III

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

amien
Specialist
Specialist
Author

"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

fseregaza
Partner - Contributor III
Partner - Contributor III

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

fseregaza
Partner - Contributor III
Partner - Contributor III

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

Best regards,

Franck SEREGAZA

Business & Decision

fseregaza
Partner - Contributor III
Partner - Contributor III

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