Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
filiperm
Contributor II
Contributor II

How to get Min of multiple values

Hello guys,

I need your help in a thing that I can't figure out how it's doing in Load script.

How I get the Min of 2 columns in load script  (blue lines).

I mean, I always want the minimum records of my ID and Description.

Capt.JPG

The result should be:

Capt2.JPG

 

Thanks in advance

Labels (4)
1 Solution

Accepted Solutions
jensmunnichs
Creator III
Creator III

Try something like this. I didn't use all of your data because I didn't want to type it all, but you get the point. It obviously wouldn't be loaded as inline in your script either, just add the last bit to what you already have and change the table/field names.

data:
LOAD * INLINE [
ID, DESC, VALUE
ABA, prod1, 12203265
ABA, prod2, 6736922
ABA, prod3, 6341063
ABA, prod4, 1929518
ACA, prod1, 813590
ACA, prod2, 380801
ACA, prod8, 46043
RFA, prod1, 7
RFA, prod2, 3
RFA, prod6, 0];

Final:
NoConcatenate
LOAD ID,
FirstSortedValue(DESC, VALUE) as DESC,
FirstSortedValue(VALUE,VALUE) as VALUE
Resident data
Group by ID;

Drop table data;

Result:

Capture.PNG

View solution in original post

4 Replies
Ivan_Bozov
Luminary
Luminary

Hi, for aggregations in the script you would need to use GROUP BY, i.e. something like this:

LOAD
   ID,
   MIN(Amount) AS MinAmount
RESIDENT YourTable
GROUP BY ID;

 

jensmunnichs
Creator III
Creator III

Try something like this. I didn't use all of your data because I didn't want to type it all, but you get the point. It obviously wouldn't be loaded as inline in your script either, just add the last bit to what you already have and change the table/field names.

data:
LOAD * INLINE [
ID, DESC, VALUE
ABA, prod1, 12203265
ABA, prod2, 6736922
ABA, prod3, 6341063
ABA, prod4, 1929518
ACA, prod1, 813590
ACA, prod2, 380801
ACA, prod8, 46043
RFA, prod1, 7
RFA, prod2, 3
RFA, prod6, 0];

Final:
NoConcatenate
LOAD ID,
FirstSortedValue(DESC, VALUE) as DESC,
FirstSortedValue(VALUE,VALUE) as VALUE
Resident data
Group by ID;

Drop table data;

Result:

Capture.PNG

View solution in original post

mfarsln
Creator II
Creator II

firstSortedValue.PNGchart.PNG

filiperm
Contributor II
Contributor II
Author

Thank you very much.

This is really what I'm looking for!