creating new field, group by?

[drohm002]

Hi, please see table below.  I would like to create a field that grabs only one score per ID, but that score has to be of the oldest date for that ID.  please help!

So this new field should return 4 for bill, 8 for sam, and 9 for jon.

ID	score	date
bill	4	1/1/2016
bill	7	6/1/2016
sam	8	2/1/2016
sam	2	7/1/2016
jon	9	3/1/2016
jon	3	8/1/2016

[MarcoWedel]

Do you mean an already loaded (resident) table or a table in a connected DB?

[qlikviewwizard]

Hi Daniel Rohm,

Please check this.

Data:

LOAD ID,score,Date#(date,'MM/DD/YYYY') as Date INLINE [

ID,score,date

bill,4,1/1/2016

bill,7,6/1/2016

sam,8,2/1/2016

sam,2,7/1/2016

jon,9,3/1/2016

jon,3,8/1/2016

];

inner join(Data)

Data_Finala:

LOAD distinct ID,min(Date) as Date resident Data group by ID;