latest updated record based on the time

[dhasharadh]

Hello All, 

I am not able get the the latest record based on the Date column by using max function, format of the date column is quite different like below. attaching the sample data for reference.

E.g. for reference code 555, the latest status at 09-JUN-23 08.26.09.776000000 PM time is PENDING_MANUAL_REVIEW

I want only this record. How can I get it.

below total 2 unique reference codes are there, for which only 2 records should come based on the maximum of date.

many thanks in advance.

@sunny_talwar @kaushiknsolanki  @Michael_Tarallo @Clever_Anjos @OmarBenSalem 

[Aurelien_Martinez]

Hi,

You need to convert your string date to a real datetime like :

Timestamp#('09-JUN-23 08.26.09.776000000 PM', 'DD-MMM-YY hh.mm.ss.fffffffff tt')

[Navars]

Hi,

Can you plz try below code.

t1:
load * inline [
Date,Reference_Code,Status
09-JUN-23 08.20.56.407000000 PM,555,Active
09-JUN-23 09.20.56.407000000 PM,555,Pending Manual Review
08-JUN-23 09.20.56.407000000 PM,123,Started
];
NoConcatenate

Temp:
Load
Timestamp(Timestamp#(Date,'DD-MMM-YY hh.mm.ss.fffffffff tt')) as Date,
Reference_Code,Status;
load * inline [
Date,Reference_Code,Status
09-JUN-23 08.20.56.407000000 PM,555,Active
09-JUN-23 09.20.56.407000000 PM,555,Pending Manual Review
08-JUN-23 09.20.56.407000000 PM,123,Started
];

inner join(Temp)
T:
Load
max(Timestamp(Timestamp#(Date,'DD-MMM-YY hh.mm.ss.fffffffff tt'))) as Date
resident t1
;
drop table t1;

[dhasharadh]

thank you Navars, not sure what is wrong, but it didnt worked. I have attached the sample data for reference.

[Aurelien_Martinez]

Hi,

Data:
Load
  TimeStamp#(Date, 'DD-MMM-YY hh.mm.dd.fffffffff tt')*1 as Date_Num,
  Date,
  Reference_Code,
  Status
From [file]
;

Inner join(Data)
Load
  Max(Date_Num)*1 as Date_Num,
  Reference_Code
Resident Data
Group By
  Reference_Code
;

[Navars]

Hi Dhasharadh,

by using below code getting data for each date wise 

code:
LOAD
Timestamp(Timestamp#(Date,'DD-MMM-YY hh.mm.ss.fffffffff tt')) as Date,
"Referience code",
Status
FROM [lib://DataFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

inner join(code)

Load max(Date) as Date,
"Referience code"
Resident code
group by "Referience code";