Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
Thanks