load one unique value of each category

[RogerG]

Hello all,

I would like to retrieve one and only one value for each of the following records, regardless of duplicates.

The criteria is the ID and the minimum date

load * inline [

ID, moddate, activityname, attempt

1,   1/1/2021, ms1, attempt 1

1,   1/1/2021, ms1, attempt 2

1,  1/5/2021, ms2, attempt 3

2,   3/1/2021, ms1, attempt 1

so the results would be 

1,   1/1/2021, ms1

2,   3/1/2021, ms2

[JuanGerardo]

Hi @RogerG, I suggest you to use a group by clause:

RawData:

load * inline [

ID, moddate, activityname, attempt

1,   1/1/2021, ms1, attempt 1

1,   1/1/2021, ms1, attempt 2

1,  1/5/2021, ms2, attempt 3

2,   3/1/2021, ms1, attempt 1

];

MyData:

load ID,

Min(Date(moddate)) as moddate

Resident RawData

Group by moddate;

drop table RawData;

JG

[RogerG]

Thanks, this project was cancelled on me, sorry for the late response