LAE

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

Originally posted by: zon


Hi,
I have a table with CDR (Call Detail Records or Calls) of my customers. I've another table that tells me witch pack/product customer had.
-----------------------------------
Imagine the following two calls made by the same customer:
First Call
Customer Billing Account - 899999999
Telephone Instance ID - 1-99ABCD9
Date - 31-01-2011 11:35:22
Duration - 110 seconds
Second Call
Customer Billing Account - 899999999
Telephone Instance ID - 1-99ABCD9
Date - 01-02-2011 12:02:05
Duration - 50 seconds

Now imagine that this customer change pack/product in the last day of January. He had Nights Phone from 01-01-2011 until 31-01-2011 23:59:59 but he changed to Unlimited Phone from that date on. So in my pack/products table I'll have two lines:
First Line
Customer Billing Account - 899999999
Telephone Instance ID - 1-99ABCD9
Initial Date - 01-01-2011 00:00:00
End Data - 31-01-2011 23:59:59
Product Desc - Nights Phone
Second Line
Customer Billing Account - 899999999
Telephone Instance ID - 1-99ABCD9
Initial Date - 01-02-2011 00:00:00
End Data - 31-12-3500 23:59:59
Product Desc - Unlimited Phone
------------------------
What I want is to know what was customer's product while he made those two calls. I tried to use node lookup, but I need to use more than just the key, I need to validate if call data is between the product inicial and end date. Can someone help me with that?

Best Regards,
ZON

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

Originally posted by: mgajdosik

Hi,

you could try X-Ref with code similar to this one, the important thing is that product list (input no 2 will have start date and end date (end date null if product is valid until now)

1:CDR_TRANS_DT - datetime of Call
2:START_DATE - start date for a product for customer
2:END_DATE - end date for product for customer

output 1 {
emit 1:*
where join.leftOrphan or (join.match and not(1:CDR_TRANS_DT.left(10).date("DD-MM-CCYY")>=2:START_DATE and
(1:CDR_TRANS_DT.left(10).date("DD-MM-CCYY")<=2:END_DATE or 2:END_DATE.isNull())))
}

output 2 {
emit 1:*,2:*
exclude referencedFields(2,{{^RightInputKey^}})
where join.match and 1:CDR_TRANS_DT.left(10).date("DD-MM-CCYY")>=2:START_DATE and
(1:CDR_TRANS_DT.left(10).date("DD-MM-CCYY")<=2:END_DATE or 2:END_DATE.isNull())
}

output 3 {
emit 2:*
where join.rightOrphan
}

Marek

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

Originally posted by: zon

Hi Marek,

Thanks.
I tried to execute that code but it turned out error:
"Right input is not sorted @ row 12"...

In this case, row is this one (with some changes I made already):

output 2 {
emit 1:*,2:ID as ID_PROD,2:Inicio_de_Portfolio,2:Fim_de_Portfolio
exclude referencedFields(2,{{^RightInputKey^}})
where join.match and (1:EVENT_STARTDATETIME.left(10).date("DD-MM-CCYY")>=2:Inicio_de_Portfolio) and
(1:EVENT_STARTDATETIME.left(10).date("DD-MM-CCYY")<=2:Fim_de_Portfolio)
} *******(This is row 12 lol, why?!)******

Thanks

ZON

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

Originally posted by: mgajdosik

Hi,

you need to have both datasets sorted on the join keys before using join. Either you can set parameter SortLeftInput and SortRightInput to True or you can sort data using Sort node up front.

Marek

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

Originally posted by: zon

Hi Marek,

Now it works. But for some reason, I've 1.135.962 records in input. The output is 930.019 (output1), 206.400 (output2) and 162.970 (output3).

The total is more than the input? Now why? How can I check for duplicates or something like that?

Best Regards,

ZON

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

Originally posted by: timonk

Hi Zon,
Just a quick note about your input vs output observation. Xref produces the results of cartesian products of the inputs. So depending on how you have specified your inputkey expressions, you'll end up with an output that exceeds input.

For example: you have 5 records with a field USAGE. Their values are 5,10,10,10,15

You cross-ref it with 3 other records, with a similar field FOO_USAGE, with values 10,10, 7 (so 2 records have the same field value).

Your matching output set would have 6 records, 3 from the matching of the first FOO_USAGE (10) against USAGE (10,10,10), and another 3 from the matching of the second FOO_USAGE. You would have left orphan output of 2 (the 5 and 15 not matching against 10,10,7) and right orphan output of 1 (the 7 not matching against 5,10,10,10, or 15). Total output records is 9.

Does that clarify what you are seeing?
Regards
Timon Koufopoulos
MDA Support.

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

Originally posted by: stonysmith

You can use the Duplicate Detection node to look for these records.

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

Originally posted by: mgajdosik

I guess you should check wheter for a single CDR multiple prices has not been assigned. You could possibly filter out in matches after doing X-Ref for the records where Call Date Time is after PricePlan StartDateTime and before PricePlan EndDateTime.

This could be one of the reasons why you got blown out records from data point of view.

Marek

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

Originally posted by: zon

Originally posted by: stonysmith
					

You can use the Duplicate Detection node to look for these records.
			

Sorry,

I'm not familiar with that node of Duplicated. Were can I find it? Under witch core?

Thanks

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

Originally posted by: stonysmith

Core/Profiling/Duplicate Detection