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