One row with 3 dates based on status

[rotter]

Hi everyone!

I have a log table with a date (Data Atualização LP), status (Cód. Status LP) and protocol (ID Prot).
Based on status I need to create a table with these dates to calculate it's date intervals.

For example:

- The image shows only the protocol nº 2040.
- Status=9 is the check-in date;
- Status=5 is the attendance date;
- Status=6 or Status=8 is the final date.

In this case the protocol nº2040 has 3 times (or three lines) to calculate and I don't know how to create this table
Using the min(date) for each status I got the first attendance:

But I need another two rows of the same protocol with the another dates.

Can you help me, please? I attached the qvd.

Thanks in advance.

[vinieme12 · Accepted solution]

You need to create another level of grouping to aggregate on

temp:
LOAD
    "ID LP",
    "Data Atualização LP",
    "Cód Status LP",
    "ID Operador LP",
    "ID Prot",
    "ID Canal Atendimento"
FROM [lib://AttachedFiles/tb_logprotocolo.qvd]
(qvd);

load *
,if("Cód Status LP"=9,AutoNumber("Data Atualização LP"),if("ID Prot"=peek("ID Prot"),peek('Group'))) as Group
Resident temp
ORder by "ID Prot","Data Atualização LP" ASC;
drop table temp;

 data

output

[rotter]

It worked! 😃

Thank you very much, @vinieme12.