topic Re: latest updated record based on the time in App Development

latest updated record based on the time

dhasharadh — Wed, 02 Aug 2023 22:43:03 GMT

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

Re: latest updated record based on the time

Aurelien_Martinez — Wed, 02 Aug 2023 15:58:50 GMT

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')

Re: latest updated record based on the time

Navars — Wed, 02 Aug 2023 17:15:40 GMT

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;

Re: latest updated record based on the time

dhasharadh — Wed, 02 Aug 2023 22:43:47 GMT

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

Re: latest updated record based on the time

Aurelien_Martinez — Thu, 03 Aug 2023 07:52:45 GMT

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 ;

Re: latest updated record based on the time

Navars — Thu, 03 Aug 2023 12:08:41 GMT

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