Filter id based on time it's loaded

[stonecold111]

Hi qlikss,

I have table like this

I'd.      Time

1.        6:44:21 AM

1.         7:23:12 AM

2.         8:22:54 AM

I need output table like this

I'd.      Time

1.         7:23:12 AM

2.         8:22:54 AM

Only IDs with max time should be there in table.

[QFabian · Accepted solution]

Hi @stonecold111 , this script, works for AM and PM, please try it :

Data:
load * inline [
Id, Time
1, 6:44:21 PM
1, 7:23:12 AM
2, 8:22:54 AM
];

Data2:
Load
Id,
Time,
Subfield(Time, ' ', 1) + if(Subfield(Time, ' ', 2)='PM', .5, 0) as Time2
Resident Data;
drop table Data;

Data:
Load
Id,
max(Time2) as Max_Time
Resident Data2
group By
Id
;
drop table Data2;

[stonecold111]

Thanks