LAE

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

Originally posted by: sarah.knapp@twtelecom.com

I'm sure there's a really simple way to do this, and I'm probably overthinking it - I'm trying to match circuit inventory to billing, and in billing the circuit ids can be appended with /01, /02, etc. I need to match the circuit id from inventory with all it's variations on the billing side, and sum up the $.

E.g., The data looks something like this:

Data Set 1
Circuit_ID
27/ABCD/100762/XX
15/ABCD/100750/XX

Data Set 2
Circuit_ID RC
27/ABCD/100762/XX $0
27/ABCD/100762/XX/01 $120
27/ABCD/100762/XX/02 $120
15/ABCD/100750/XX $100

Desired result:
Circuit_ID RC
27/ABCD/100762/XX $240
15/ABCD/100750/XX $100

Ideally I'd like to use an x-ref for the initial join, then aggregate afterwards. I've tried using regexIsMatch, various string functions, etc, and am either getting errors or the same result as if I did a straight match on the circuit IDs.

How would you recommend I do this? Thank you!

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

Originally posted by: ltolleson

Sarah,

The attached graph is a possible solution. The trick to the solution is in the join node. If you look you will see the key values for the join contain a value of (1). This will cause the join node to match everything from both inputs and then we can limit the output in the where clause, which is what I've done. This type of join node does cause a Cartesian join which could possibly limit the amount of data this node will handle. If you get a bad_alloc error then the Cartesian join is too big and you will need to find another solution for the matching.

Hope that helps...

Larry
Attachments:
MatchCircuitIDs.brg

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

Originally posted by: ejones

Building on what Larry said, you could also split the circuit id using the "/" separator and then join and agg on the first 4 individual parts. Here is the code you could use in the filter node to parse out the 4 individual parts.

circuit_part_a = null.str()
circuit_part_b = null.str()
circuit_part_c = null.str()
circuit_part_d = null.str()
circuit_part_list = Circuit_ID.split("/")
if circuit_part_list.isNotNull() and circuit_part_list.len() >= 4 then {
circuit_part_a = circuit_part_list[0]
circuit_part_b = circuit_part_list[1]
circuit_part_c = circuit_part_list[2]
circuit_part_d = circuit_part_list[3]
}
emit circuit_part_a as Circuit_Part_A
emit circuit_part_b as Circuit_Part_B
emit circuit_part_c as Circuit_Part_C
emit circuit_part_d as Circuit_Part_D

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

Originally posted by: sarah.knapp@twtelecom.com

Thanks guys - got it working :-D