How to get the minimum or maximum value for every distinct row reading from multiple files?

[GeorgeQV]

Dear all,

let's say I have three txt-files like this:

List1:

ID    Value

1     100

2     200

3     300

List2:

ID    Value

1     80

2     250

List3:

ID     Value

1     75

2     150

1     12

How can I read those files sequentially and store the minimum or maximum value over all records for every distinct ID? No matter in which order I read the lists, the result should be:

ID    Value

1     12

2     150

3     300

Thank you in advance.

[Gysbert_Wassenaar]

Temp:

LOAD

     ID, min(Value) as V1

FROM

     List1

GROUP BY

     ID;

JOIN (Temp)

LOAD

     ID, min(Value) as V2

FROM

     List2

GROUP BY

     ID;

JOIN (Temp)

LOAD

     ID, min(Value) as V3

FROM

     List3

GROUP BY

     ID;

Result:

LOAD

     ID, Rangemin(V1,V2,V3) as MinValue

RESIDENT

     Temp;

DROP TABLE Temp;

[GeorgeQV · Accepted solution]

Dear Gysbert,

thank you for your answer. It is great when there are really only three files, but actually I have hundreds of them, three was just an example. I have a solution now:

When reading in the files, use the wildcard in the LOAD statement

Table:
LOAD ID, Value
FROM ..\List*.txt
(txt, codepage is 1252, embedded labels, delimiter is ';', no quotes);

All my lists are then sequentially read and their rows added to the table.

After that you can create another table for aggregation:

MinMaxValue:
LOAD min(Value) as MinValue,
     max(Value) as MaxValue
Resident Table group by ID;

The group by statement is important. Finally you have a table with all aggregations grouped by the ID.