Value from other column max value

[sculptorlv]

Good day.

I have data:

Nr.     Date.                 Value

F1     15.04.2018          120

F1     17.03.2018          150

F1     01.06.2018          100

F4     18.06.2017          30

I need in LOAD statement get for each Nr. only Value from latest data:

Nr.     Date.                 Value

F1     01.06.2018          100

F4     18.06.2017          30

How can I do it?

Thank you in advance!

[MK_QSL · Accepted solution]

SET DateFormat='DD.MM.YYYY';

Data:
Load * Inline
[
 Nr,  Date,      Value
 F1,  15.04.2018, 120
 F1,  17.03.2018, 150
 F1,  01.06.2018, 100
 F4,  18.06.2017, 30
 F4, 20.06.2017, 10
];

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

NoConcatenate
Final:
Load * Resident Data Where Date = MaxDate;

Drop Field MaxDate;
Drop Table Data;

[sunny_talwar]

Another possibility is to do a Right or Inner join

SET DateFormat='DD.MM.YYYY'; 

Data: 

Load * Inline 

[ 

Nr,  Date,      Value 

F1,  15.04.2018, 120 

F1,  17.03.2018, 150 

F1,  01.06.2018, 100 

F4,  18.06.2017, 30 

F4, 20.06.2017, 10 

]; 

Right Join(Data) 

Load Nr,  

Date(Max(Date)) as Date

Resident  Data

Group By Nr; 

[shiveshsingh]

May be this

Temp:load Date((Date#(Date,'DD.MM.YYYY')),'DD.MM.YYYY') as Date,Nr,Value inline [Nr,Date , Value

F1, 15.04.2018 ,120

F1, 17.03.2018 ,150

F1, 01.06.2018 , 100

F4, 18.06.2017,30];

inner join

T: load Date(max(Date#(Date,'DD.MM.YYYY')),'DD.MM.YYYY') as Date,Nr

Resident Temp

group by Nr;

[sculptorlv]

Thank you All!

I will use the first construction

[sculptorlv]

Can you please correct me with the syntax.

NoConcatenate  is underlined by red line ..