LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ronald

Trying to perform some call analysis we get stucked on the following puzzle:

Our calldetail datafeed is providing information of A and B party and we would like to combine them to one informationrecord based on the amount of overall calls.
Datafeed example:
header) phonenr A - phonenr B - timestamp - total calls A - total calls B - ok/not ok
1) ph1 - ph2 - 10:00:00 - 300 - 16 - ok
2) ph2 - ph1 - 10:00:00 - 16 - 300 - not ok
3) ph3 - ph4 - 10:05:00 - 1 - 10 - ok
4) ph4 - ph3 - 10:05:00 - 10 - 1 - ok

if phonenr A has more calls than B in the overalldataset (aggregated in query aside of this one) and the timestamps match (situation 1 & 2) we would like to interpret the two records as:
ph1 has connection with ph2 at 10:00:00, problem found on ph2 side

if phonenr B has more calls than A in the overalldataset (aggregated in query aside of this one) and the timestamps match (situation 3 & 4) we would like to interpret the two records as:
ph4 has connection with ph3 at 10:05:00, no problems found

Anyone got some ideas to solve this ? Any help would be welcome, thx in advance!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hi,

It looks like the example record data you have provided contains the solution.
Do you have example record data with the data sets that you are trying to manipulate to get to the solution?

I'm just not sure what data you are operating with to try and get to this stage..


Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ronald

Probably the solution is in de question but I can't make it work
Find below the dataset and example code to enrich the data with overall calls per A and B party.

The green marked lines are records than need to be combined to one.
For the first occurrence it seems ANR xxxxxxxx8881 has the most calls and need to be selected in the output result. (while the one with same timestamp and xxxxxxxx8881 as BNR need to be discarded)

ANR BNR FAULT DATE TIME DURATION(SEC) POSTCODE ATOTALCALLS BTOTALCALLS
xxxxxxx2958 xxxxxxx8881 YES 20-5-2013 9:21:44 1105 3830 6 77
xxxxxxx8881 xxxxxxx2958 NO 20-5-2013 9:21:44 1105 2431 77 6
xxxxxxx3291 xxxxxxx4569 YES 20-5-2013 10:41:39 91 3146 67 1
xxxxxxx5256 xxxxxxx5539 NO 20-5-2013 10:55:58 312 3465 176 400
xxxxxxx5539 xxxxxxx5256 YES 20-5-2013 10:55:58 312 2633 400 176
xxxxxxx5648 xxxxxxx5257 NO 20-5-2013 11:10:32 60 3828 176 1
xxxxxxx2676 xxxxxxx5258 NO 20-5-2013 11:11:53 28 2709 506 1



CDR_dataset and examplecode.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

So ..
Assuming I have understood correctly, I think the attached graph does what you want.

This simply adds an identifier to each of the incoming records.
Then, it searches for all cases where there are records where the DATE and TIME match, and the ANR in the first record matches the BNR in the second record and vice versa.
For each of these, it outputs the identifier of the record where ATOTALCALLS is the lowest, calling this "discardId"

Then, it goes through and outputs all records who's identifier does not match any of the "discardId" identifiers.

Hope this helps,

Tim.
Attachments:
CallCorrelation.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ronald

This is exactly what I needed. Thanks for the help!!
Enrich the record futher is quite simple now.

Thx