Sort by Date and Remove Duplicates

[Anonymous]

Hi,

I have a number field that has multiple scores on different dates, I need to show only the number with the latest date score, so we need to sort date in desc order and then show only the number and the score at the latest date, removing the duplicates of other dates.

Attached example in excel file

This is urgent, please reply asap

Thanks

[Gysbert_Wassenaar]

Try this:

Result:

LOAD Number,

     Score,

     Date

FROM [comm161622.xlsx] (ooxml, embedded labels, table is Sheet1);

RIGHT JOIN

LOAD Number, max(Date) as Date

RESIDENT Result

GROUP BY Number

;

[marcus_sommer]

You could use a load-statement like:

Load Number, max(date) From x group by Number;

to find your last date and use this for a filter or to join/map these date to your source-table. But you should also have a look on firstsortedvalue() with which you could pick directly within the gui.

- Marcus

[Anonymous]

Thanks for the reply guys

Thanks Gysbert, I think your solution worked just fine, appreciate it

[Anonymous]

Hi guys,

gwassenaar‌ mby‌ jagan‌

Need your help again, I noticed a few blanks in my data, so now the output should be the latest dated score, in case the latest date has a blank score, then the previous latest date score should be picked up. Desired output colored in Red, please reply asap.

Appreciate your help

Thanks

  

Number	Score	Date
1234		1-Apr-14
1234	2	2-Jan-14
1234	1	1-Jan-14
1111	3	31-Mar-14
1111	4	1-Apr-14
9990		15-Apr-14
9990	6	2-Jan-14
9990	7	1-Apr-14