Second connection

[Anonymous]

Hi,

I'm reporting on a telephone database and need a script that gives me the time of the first connection after the first throughconnect.

Examples below:

INI - Inbound Call start    

CON - Connection

HEL - Held

INT - ThroughConnect

DIS - DisConnection

Example 1:

CallId	Event	EventTime
123	INI	12:01:33
123	CON	12:01:38
123	HEL	12:01:43
123	CON	12:01:50
123	INT	12:02:03
123	CON	12:02:10
123	INT	12:03:15
123	CON	12:03:20
123	DIS	12:03:40

Example 2:

CallId	Event	EventTime
123	INI	12:01:33
123	CON	12:01:50
123	INT	12:02:03
123	CON	12:02:10
123	DIS	12:03:40

Example 3:

CallId	Event	EventTime
123	INI	12:01:33
123	CON	12:01:38
123	CON	12:01:43
123	CON	12:01:50
123	INT	12:02:03
123	CON	12:02:10
123	INT	12:03:15
123	CON	12:03:20
123	DIS	12:03:40

Thanks for your help!

Best regards,

Arjan

[Miguel_Angel_Baeyens]

Hi Arjan,

I have not tested it but it seems a case to use RangeSum(), Peek() and Previous() functions in the script. The script should look like the following:

TableTmp:
LOAD CallId, 
     Event, 
     EventTime
FROM
testCallId.xlsx
(ooxml, embedded labels, table is Sheet1);
Table:
LOAD *,
     If(CallId = Previous(CallId), If(Event = 'CON' AND Previous(Event) = 'INT', RangeSum(Peek('EventTimeCntr'), 1), Peek('EventTimeCntr')), 0) AS EventTimeCntr,
     If(CallId = Previous(CallId), If(Event = 'CON' AND Previous(Event) = 'INT' AND RangeSum(Peek('EventTimeCntr'), 1) = 1, EventTime)) AS EventTimeFirstINT
RESIDENT TableTmp
ORDER BY CallId, EventTime;
DROP TABLE TableTmp;

I may have missed some parentheses or commas, but that's the idea.

Hope that helps.

Miguel

[Anonymous]

Hi,

What do you mean with:

RangeSum(Peek('EventTimeCntr'), 1))) AS EventTimeCntr

I only get NullValues.....

Arjan

[perumal_41]

Hi ,

Please Explain detaily .How u need Result .if u Possible Upload sample Data

Regards

Perumal A

[Anonymous]

Here some testdata.....hope you can help me!

Arjan

[Miguel_Angel_Baeyens · Accepted solution]

Hi Arjan,

Please check the application attached.

Regards.

Miguel

[Anonymous]

Hi Miguel,

Thanks for your help!!!!!!!

Regards,

Arjan