Identify Duplicates

Tykili
Occasional Contributor

Identify Duplicates

Hi friends,

Hoping to get some help with a method to help me finish up a labor calculation module.

 

I'm tasked with allocating costs to contractors differently dependent on if there are multiple contractors from the same "Vendor" and "Area". IF multiple contractors from same Vendor in an Area THEN divide Area cost evenly by month ELSE other calculation. 

 

I'm currently using ISFIRSTOCCURRENCE to identify if a contractor with respective Vendor is a duplicate in a specific Area. This works to identify the dupe but then I cannot use the false booleans to choose as a cost allocation as is discounts the first occurrence contractor. 

 

Do you have any ideas of how to go about this? Please let me know if I can clarify any part of the issue. Thanks for your responses!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
fabio.casalegno
Occasional Contributor

Hi Tykili,

 

I believe you need a staging module to perform this task. I'm assuming your source module is a list of contractors which have a Vendor and Area assigned. Let me know if it is not the case.

 

1) First, in the source module create a number-formatted line item called "Count" that is always equal to 1.

2) Second, create a staging module in which you do "#contractors = Count[SUM:  Vendor, SUM: Area]"

3) This will give you a module in which you see how many Contractors there are for each Vendor/Area combination. Your final formula would be something like "IF #contractors > 1 THEN divide Area cost evenly ELSE other calculation"

 

Hope it helps.

 

View solution in original post

4 REPLIES 4
fabio.casalegno
Occasional Contributor

Hi Tykili,

 

I believe you need a staging module to perform this task. I'm assuming your source module is a list of contractors which have a Vendor and Area assigned. Let me know if it is not the case.

 

1) First, in the source module create a number-formatted line item called "Count" that is always equal to 1.

2) Second, create a staging module in which you do "#contractors = Count[SUM:  Vendor, SUM: Area]"

3) This will give you a module in which you see how many Contractors there are for each Vendor/Area combination. Your final formula would be something like "IF #contractors > 1 THEN divide Area cost evenly ELSE other calculation"

 

Hope it helps.

 

Tykili
Occasional Contributor

@fabio.casalegno It sounds like this will work, thank you!

 

The only thing is my list of contractors is part of a Labor List which includes internal and external labor (external being contractors). Do you suggest making a list subset of only external labor and a subsidiary view in my source module to work off of?

fabio.casalegno
Occasional Contributor

I think it can be solved in a much simpler way if you include a condition in the Count line item that says:

IF External THEN 1 ELSE 0

Let me know if it works.

Tykili
Occasional Contributor

This all worked perfectly, thank you. I'll definitely be adding this to the arsenal moving forward