Max Data with Time

[sumeet-vaidya]

Dear All,

Please find below data. I need to Pick the max date and associated time for the given ID in script.

ID	Date	Time	Name
101	01-02-2017	11:04:25	ABC
101	04-05-2017	18:12:14	PQR
101	07-08-2017	09:45:45	XYZ
101	07-08-2017	09:45:50	LMN

The result should be :   

ID	Date	Time	Name
101	07-08-2017	09:45:50	LMN

Regards

Sumeet

stalwar1‌

[MK_QSL · Accepted solution]

Data:
Load
 ID,
 Date(Date#(Date,'DD-MM-YYYY')) as Date,
 Time,
 Name
Inline
[
 ID, Date, Time, Name
 101, 01-02-2017, 11:04:25, ABC
 101, 04-05-2017, 18:12:14, PQR
 101, 07-08-2017, 09:45:45, XYZ
 101, 07-08-2017, 09:45:50, LMN
];

Left Join (Data)
Load ID, Date(Max(Date)) as MaxDate Resident Data Group By ID;

Left Join (Data)
Load ID, Time(Max(Time)) as MaxTime Resident Data Where Date = MaxDate Group By ID;

NoConcatenate
Final:
Load ID, Date, Time, Name Resident Data
Where Date = MaxDate and Time = MaxTime;

Drop Table Data;

[tresesco]

Try using firstsortedvalue(), like:

[MK_QSL]

Yeah that's the easiest way.. But Sumeet wants to achieve at script level.. Otherwise this is easy to do at front end.

[tresesco]

For script try like:

t1:

Load * Inline [

ID, Date, Time, Name

101, 01-02-2017, 11:04:25, ABC

101, 04-05-2017, 18:12:14, PQR

101, 07-08-2017, 09:45:45, XYZ

101, 07-08-2017, 09:45:50, LMN];

NoConcatenate

t2:

load

  ID,

  FirstSortedValue(Date, -(Date+Time)) as Date,

  FirstSortedValue(Time, -(Date+Time)) as Time,

  FirstSortedValue(Name, -(Date+Time)) as Name

Resident t1 Group by ID;

Drop Table t1;

[MK_QSL]

Excellent.. This is better solution. But issue in using FirstSortedValue is ...

say we have below one more line.

101, 07-08-2017, 09:45:50, XXX

In this case, we will not get result.

If we use Distinct then will get one line one...

But we need here two lines...

So I avoided FirstSortedValue..

[tresesco]

......

But we need here two lines...

Lets wait to know OP's expectation.

[sumeet-vaidya]

Thanks Manish, That works perfectly for me.

Regards

Sumeet

[sumeet-vaidya]

Hi Tresco,

Thanks for your reply but i wanted to achieve it at the script level.

Regards

Sumeet

[MK_QSL]

If you got your answer, then kindly close the thread by selecting correct/helpful answer.