select the latest date data

[userid128223]

I have data in following format:

Date	Class	Grade	Teacher	Notes
2017-05-29 10:25:32:234	Math	6	Ms Walsh	dafd
2017-05-20 9:06:42:345	Science	10	Ms Helen	dafdaf
2017-05-20 23:33:46:453	Chemistry	6	Mr Tom	dfdfd

How do you pick the record with latest date & time

Result:

Date	Class	Grade	Teacher	Notes
2017-05-20 23:33:46:453	Chemistry	6	Mr Tom	dfdfd

[tresesco]

Try like:

Date : Timestamp(Max(Date))

Class : FirstSortedValue(Class, -Date)

Garde: FirstSortedValue(Grade, -Date)

Teacher: FirstSortedValue(Teacher, -Date)

Notes: FirstSortedValue(Notes, -Date)

[userid128223]

Thanks for response

I want this to be done at script level.

[tresesco]

This works in script too.

Load

  Timestamp(Max(Date)) as Date,

  FirstSortedValue(Class, -Date) as Class,

  FirstSortedValue(Grade, -Date) as Grade,

  FirstSortedValue(Teacher, -Date) as Teacher,

  FirstSortedValue(Notes, -Date) as Notes

  Inline [

Date, Class, Grade, Teacher, Notes

2017-05-29 10:25:32:234, Math, 6, Ms Walsh, dafd

2017-05-20 9:06:42:345, Science, 10, Ms Helen, dafdaf

2017-05-20 23:33:46:453, Chemistry, 6, Mr Tom, dfdfd ]

[userid128223]

i will try it out. I was thinking of doing via group by clause with max(date).

[userid128223]

Hi Tresesco

2 question:

1) i tried your solution and with inline data it works but when you do it out of a file or with data generated by another load previously it does not work.

Below does not work:

temp:

LOAD Date,

     Class,

     Grade,

     Teacher,

     Notes

FROM

(ooxml, embedded labels, table is Sheet1);

new:

Load

  Timestamp(Max(Date)) as Date,

  FirstSortedValue(Class, -Date) as Class,

  FirstSortedValue(Grade, -Date) as Grade,

  FirstSortedValue(Teacher, -Date) as Teacher,

  FirstSortedValue(Notes, -Date) as Notes

  Resident temp;

  drop table temp;

2) can this solution be derived by another method like max date & group by?

[tresesco]

Just try putting a NoConcatenate between two loads like:

temp:

LOAD Date,

     Class,

     Grade,

     Teacher,

     Notes

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

new:

Load

  Timestamp(Max(Date)) as Date,

  FirstSortedValue(Class, -Date) as Class,

  FirstSortedValue(Grade, -Date) as Grade,

  FirstSortedValue(Teacher, -Date) as Teacher,

  FirstSortedValue(Notes, -Date) as Notes

  Resident temp;

  drop table temp;