How to get Min of multiple values

[filiperm]

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.

The result should be:

Thanks in advance

[Ivan_Bozov]

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 · Accepted solution]

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:

[mfarsln]

[filiperm]

Thank you very much.

This is really what I'm looking for!