Interval between different calls

[rotter]

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.

[vidyutverma]

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.

[rotter]

Hey, @vidyutverma!

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

[sidhiq91]

@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.

[vidyutverma]

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.

[sidhiq91]

@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?

[rotter]

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.

[vidyutverma]

@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 

[vidyutverma]

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 )

[rotter]

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.