Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
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