topic Re: Interval between different calls in App Development

Interval between different calls

rotter — Thu, 28 Jul 2022 15:36:00 GMT

Hi everyone! Can you help me, please?

I have a log table with calls.

Each call has:

An operator (ID Operator);
A local (ID Local);
A protocol (ID Protocol).

Call starts at status 2 and ends on 3 (Status).
All calls follow the order of the date (Date).

I need to create a table with two columns showing the interval between a call and other.
For example (considering only one operator):

In the red square has a interval. The operator 285 ends the protocol 2339169 and then starts the call of protocol 2353897.

I need something like that for each interval, with operator and local, without the interval between one day and other.

I attached the qvd.
Thanks in advance.

Re: Interval between different calls

vidyutverma — Thu, 28 Jul 2022 18:30:27 GMT

So If I understand you correctly, Call (protocol ID 2353897) starts at 8:59:19 and ends at 9:12:19, right?
So every protocolID has two rows, one with 2 and another with 3 as Status (some may have these missing in case you have data quality issues). To solve this problem, try breaking it into multiple steps:
Step 1 : Create a table with all the rows ordered by Date/Time, ProtocolID and Status descending.
Step 2: Use Previous() to access the Date/Time of previous record and based on that create a duration field [ date-previous(date) as Duration ], also create date end field.
Step 3: Just use the records with Status 2 (as Starus 3 records won`t have the duration field) to create the desired table.

Re: Interval between different calls

rotter — Tue, 02 Aug 2022 11:32:14 GMT

Hey, @vidyutverma!

I'll try your suggestion and then come back to let you know if it worked.
Thank you very much!

Re: Interval between different calls

sidhiq91 — Tue, 02 Aug 2022 12:37:08 GMT

@rotter You can also try the below mentioned method. Please let me know if it has worked.

NoConcatenate
Temp:
LOAD
ID,
"Date",
Status,
ID_Local,
ID_Operator,
ID_Protocol
FROM [lib://Qlik Community Practice/tb_operator.qvd]
(qvd);

NoConcatenate
Temp1:
Load Date as [Start Date],
ID_Local,
ID_Operator,
ID_Protocol,
ID_Local&'-'&ID_Operator&'-'&ID_Protocol as Key
Resident Temp
where match(Status,'2') and match(ID_Operator,'285');

inner join (Temp1)
Temp2:
Load Date as [End Date],
ID_Local as ID_Local1,
ID_Operator as ID_Operator1,
ID_Protocol as ID_Protocol1,
ID_Local&'-'&ID_Operator&'-'&ID_Protocol as Key
Resident Temp
where match(Status,'3') and match(ID_Operator,'285');

Drop field ID_Operator1,ID_Local1,ID_Protocol1,Key from Temp1;

Drop table Temp;

Exit Script;

I Tried for ID_Operator 285, you can definitely try without that and let me know if it has worked or not.

Re: Interval between different calls

vidyutverma — Tue, 02 Aug 2022 15:01:28 GMT

Yes, Your suggestion would work assuming you do not have protocol ID repeating in data. I tend to order the records by time to ensure that neighbouring records are picked even when ProtocolID or any other such field do repeat.

Re: Interval between different calls

sidhiq91 — Tue, 02 Aug 2022 15:19:44 GMT

@vidyutverma Yup that is correct since you are close to data you need to let me know if my solution worked or should we look into some alternative?

Re: Interval between different calls

rotter — Wed, 03 Aug 2022 13:42:38 GMT

Hey, guys!
Thanks for your helping.

I tried the solution proposed by @vidyutverma , but I can't develop what you suggested... Could you post it here, please?

@sidhiq91 your suggestion is not wrong, except that I need the time (or interval) between the end and the start of each protocol, considering the operator and his local.

To be clearer: I have many operators working to serve people. Each person has a protocol and the operator works with it. For example: I'm an operator and start the service of a protocol at 08:30 and finish at 08:35. My next appointment started at 08:50, so between 08:35 and 08:50 I wasn't working and this is the information I'm looking for. How long are operators idle??

Using the @sidhiq91 table, between protocol 2339169 and 2353897 it would be:

Green Square: start;
Red Square: end.

I don't need the protocol, just ID_Local, ID_Operator, Start Date and End Date if possible.

Thanks again, folks.

Re: Interval between different calls

vidyutverma — Mon, 08 Aug 2022 11:41:47 GMT

@rotter I understand that you are trying to find the duration between two calls by the same operator. The approach I suggested should work, with a couple of minor changes. When you say `I can`t develop what you suggested`, I assume you are not comfortable with some of these concepts : Using Interrecord Functions/ Difference between Peek&Previous. Try reading about these, I am sure you should be able to solve this. The steps that I have mentioned are detailed enough.

Cheers,

Vidyut

Re: Interval between different calls

vidyutverma — Mon, 08 Aug 2022 11:50:19 GMT

A small subproblem for you to solve:

If you have two rows of data, first row with Status ID 3 , and second row with Status ID 2 for the same operator, using the Date (containing date/time of the record), How would you compare and create a duration field ?(hint: using an if statement, and previous() you should be able to )

Re: Interval between different calls

rotter — Thu, 11 Aug 2022 18:39:55 GMT

Hey, @vidyutverma!

I solved the problem using a different approach.
The table has four status:

0: Login
1: Logout
2: Start appointment
3: End of appointment

Instead of calculating the gap time between two protocols, I calculated all logins and logouts for id_local, id_operator and date; so I calculated all appointments using the same fields. To find out how much time operators were idle, I decreased the amount of time logged from working time.

Anyway, I'm greatful for your help and attention.

Re: Interval between different calls

vidyut — Fri, 12 Aug 2022 09:04:34 GMT

Good Work. There are many ways to solve a problem, and many of us stop when we see a problem that is not straightforward to solve. Instead, thinking about the problem and breaking it down into smaller ones really helps in solving it, as well as pushing yourself to try and think of different approaches.

I intentionally resisted giving you the code, as that kills the joy of solving the problem. Maybe later when I am doing a session on interesting problems, I will invite you.

Cheers