Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try like:
Date : Timestamp(Max(Date))
Class : FirstSortedValue(Class, -Date)
Garde: FirstSortedValue(Grade, -Date)
Teacher: FirstSortedValue(Teacher, -Date)
Notes: FirstSortedValue(Notes, -Date)
Thanks for response
I want this to be done at script level.
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 ]
i will try it out. I was thinking of doing via group by clause with max(date).
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?
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;