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

[vishsaggi]

may be try this?

Score:

LOAD ID,

     score,

     date

FROM

[https://community.qlik.com/thread/319081]

(html, utf8, embedded labels, table is @1);

Right Join(Score)

LOAD ID,

     Min(date) AS date

Resident Score

Group By ID;

[drohm002]

does this create a new field or new table or?  how can I get the field I want

[vishsaggi]

This will cut the data and give you only those values which has min date.

YOu can try below:

Score:

LOAD ID,

     score,

     date

FROM

[https://community.qlik.com/thread/319081]

(html, utf8, embedded labels, table is @1);

LEFT Join(Score)

LOAD ID,

     Date(Min(date)) AS NewDate

Resident Score

Group By ID;

NoConcatenate

Final:

Load *,

     IF(date = NewDate, score) AS NewScore

Resident Score;

Drop Table Score;   

[MarcoWedel]

Hi,

one solution might be:

table1:
LOAD ID,
    FirstSortedValue(score, date) as FirstScore
FROM [https://community.qlik.com/thread/319081] (html, codepage is 1252, embedded labels, table is @1)
Group By ID;

hope this helps

regards

Marco

[drohm002]

hi Marco, do I use the line below?  like it is in yours...

FROM [https://community.qlik.com/thread/319081] (html, codepage is 1252, embedded labels, table is @1)

[afroz_shaik]

Hi Danieal,

you have to use "From YourTable".

[drohm002]

what do you mean?? please show example

[afroz_shaik]

In Script your table must be named something right either it may be Excel, Sql Server eetc.. source. Your load statement looks like this

TableName:

Load ID,

FirstSortedValue(Score,date) As FirstScore

from Source

what is your source?

[drohm002]

my source is a table called PELODDaily.....