Choose data based upon max time

[MikeJones]

Hi, current my table is showing

jobid	Area	EventDate	Event Time	EventDesc	RescheduleCancelReason	ActionedBy
94570170	A	2023-08-03	10:00:00	Customer Cancellation	12. Call Back	Mike Jones
94570170	A	2023-08-03	11:00:00	XXX Reschedule	13. No Engineer	Mike Jones
94570170	A	2023-08-04	13:30:00	XXX Reschedule	76. Over planned	Gill Jones
94570456	C	2023-08-03	12:00:00	Customer Cancellation	12. Call Back	Mike Jones
94570456	C	2023-08-03	10:00:00	XXX Reschedule	Not Entered	Mike Jones
98765432	B	2023-08-03	15:00:00	Customer Cancellation	12. Call Back	Adam Jones
98765432	B	2023-08-03	16:00:00	XXX Reschedule	23. Engineer Sick	Adam Jones
76543219	D	2023-08-03	12:00:00	XXX Reschedule	1. No Stock	William Jones

I have been trying to use Max(EventTime) and have tried numerous iterations with no luck.  My desired output is shown below and I need to use set analysis.

jobid	Area	EventDate	Event Time	EventDesc	RescheduleCancelReason	ActionedBy
94570170	A	2023-08-03	11:00:00	XXX Reschedule	13. No Engineer	Mike Jones
94570170	A	2023-08-04	13:30:00	XXX Reschedule	76. Over planned	Gill Jones
94570456	C	2023-08-03	12:00:00	Customer Cancellation	12. Call Back	Mike Jones
98765432	B	2023-08-03	16:00:00	XXX Reschedule	23. Engineer Sick	Adam Jones
76543219	D	2023-08-03	12:00:00	XXX Reschedule	1. No Stock	William Jones

[MatheusC]

Hi, @MikeJones 

Try the expression below, print the result achieved

I hope it helps

Timestamp(

max(If(Aggr(nodistinct Timestamp( max([Hora do evento]),'h:mm:ss'),[ID de trabalho],ActionedBy) = [Hora do evento], [Hora do evento])),'h:mm:ss') 


alt(
Timestamp(
max(If(Aggr(nodistinct Timestamp( max([Hora do evento]),'h:mm:ss'),[ID de trabalho],ActionedBy) = [Hora do evento], [Hora do evento])),'h:mm:ss'),0)  ->  with alt function to handle nulls

[MikeJones]

Hi Matheus, thanks for your reply.

I have used the second expression you mentioned above.  However, it doesn't seem to be working.

The job 60409929 shown below has two different EventDates, though the calc comes in zero.

Secondly, how I can get the table to show the max time should the EventDate be the same as in the second job?

Thanks

[MatheusC]

@MikeJones 
Anexar EventDates na agregação

O trabalho 60409929 mostrado abaixo tem dois EventDates diferentes, embora o cálculo venha em zero.
alt(
Timestamp(
max(If(Aggr(nodistinct Timestamp( max([Hora do evento]),'h:mm:ss'),[ID de trabalho],ActionedBy) = [Hora do evento], [Hora do evento ], EventDates )),'h:mm:ss'),0) -> com função alt para lidar com nulos

Em segundo lugar, como posso fazer com que a tabela mostre o tempo máximo caso EventDate seja o mesmo do segundo trabalho?

Neste 2º caso, quais são os parâmetros a considerar, pois no seu primeiro exemplo mesmo as mesmas datas do evento consideravam apenas a data com o Max Time do evento.

Att @MatheusC 

[MatheusC]

@MikeJones 

If you have solved your question, close the topic with the solution to help the qlik community.

Thanks

[MikeJones]

Hi Matheus, sorry for the delay getting back to you.  Sadly, I can't read Portuguese.