topic Re: load one unique value of each category in App Development

load one unique value of each category

RogerG — Mon, 03 May 2021 16:11:33 GMT

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

Re: load one unique value of each category

JuanGerardo — Mon, 03 May 2021 17:46:14 GMT

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

Re: load one unique value of each category

RogerG — Wed, 20 Apr 2022 19:34:09 GMT

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