Identify Duplicates

Tykili
Tykili
in Modeling

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!

 

 

Tagged:

Best Answer

  • fabio.casalegno
    fabio.casalegno
    Answer ✓

    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.

     

Answers

  • Tykili
    Tykili

    @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
    fabio.casalegno
    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
    Tykili
    This all worked perfectly, thank you. I'll definitely be adding this to the arsenal moving forward

Categories