Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
dhasharadh
Creator III
Creator III

latest updated record based on the time

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 

 

dhasharadh_1-1690981504865.png

 

 

Labels (1)
5 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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')
Help users find answers! Don't forget to mark a solution that worked for you!
Navars
Creator
Creator

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;

Navars_1-1690996441765.png

 

dhasharadh
Creator III
Creator III
Author

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

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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
;
Help users find answers! Don't forget to mark a solution that worked for you!
Navars
Creator
Creator

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";

 

Navars_0-1691064176494.png