topic Re: One row with 3 dates based on status in App Development

One row with 3 dates based on status

rotter — Tue, 21 Jun 2022 20:22:21 GMT

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.

Re: One row with 3 dates based on status

vinieme12 — Wed, 22 Jun 2022 06:46:22 GMT

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

Re: One row with 3 dates based on status

rotter — Thu, 23 Jun 2022 13:07:46 GMT

It worked! 😃

Thank you very much, @vinieme12.