Unique tool without excluding dupliactes

[DanMax]

Hey guys, 

I am matching a set of case firms with a set of control firms based on industry classification and asset size. I want to match each case firm with the closest control firm in terms of asset size (within an industry). However, I want to make sure, that each control firm is only included once. The problem is, that if I sort by Asset Difference and then apply the Unique tool to filter for unique control firms, I eliminate all the other possible combinations for the second case firm.

In the event of two case firms leading to the same control firm, I want the second case firm two go for the second closest control firm in terms of asset size. Please see the Example below. The best match for Firm ABC and Firm XYZ would be Firm E. (closet asset difference). As the same control firm can not be included twice, Firm XYZ should go for the second closest control firm, which is in this example Firm D. 

Input

Case Firm	Total Assets	Industry
Firm ABC	100	1
Firm XYZ	1000	1

Control Firms	Total Assets	Industry
Firm D	1800	1
Firm E	1100	1
Firm F	2000	1

Match

Case Firm	Control Firm	Asset Difference (absolute)	Industry
Firm ABC	Firm D	1700	1
Firm ABC	Firm E	1000	1
Firm ABC	Firm F	1900	1
Firm XYZ	Firm D	800	1
Firm XYZ	Firm E	100	1
Firm XYZ	Firm F	1000	1

Wanted Output 

Case Firm	Control Firm	Asset Difference (absolute)	Industry
Firm ABC	Firm E	1000	1
Firm XYZ	Firm D	800	1

Do you have any idea how to implement this on Alteryx? 

Thank you very much and have a nice day

[AngelosPachis]

Hi @DanMax ,

What if instead of a unique tool you use a sample tool to keep the first record per Case Firm? Would that work?

EDIT : Scratch that, missed the "each control firm is included once" bit

[Luke_C]

@AngelosPachis I was going this route as well, but the complexity lies in the requirement that for the 2nd record, the control firm can't be used for multiple case firms. I'm thinking perhaps an iterative macro could be leveraged to remove the first control firm from the set, then re-apply the sample logic you have. 

[AngelosPachis]

Yes @Luke_C you are right, I missed it when I first read the post. Trying to think of a logic to avoid the iterative macro but not sure I can come up with something

[DanMax]

Yeah, that makes sense. A macro that would remove the first combination of Firm ABC - Firm E. So that only Firm D and F can be matched to Firm XYZ. 

[Luke_C]

@DanMax 

Take a look at this. The macro will use @AngelosPachis 's logic of sorting and sampling, but then I use join tools to remove the already processed case firm and control firm. The remaining data is passed through again to be sorted and sampled. 

[mceleavey]

Hi @DanMax 

I've built an iterative macro to do this and it produces the following:

The workflow appends the records together and feeds into the macro:

and the macro looks like this:

I've attached the package, the workflow and the macro.

Hope this helps.

M.

[AngelosPachis]

@DanMax 

Late to the party with another iterative one - looks similar to what @Luke_C has done I think

Output

[DanMax]

Hey guys,

Thank you very much for your support. Really appreciate your effort. 

I have just figured out a mistake in my instructions. As the asset difference between Firm XYZ and Firm E (100) is smaller then Firm ABC and Firm E (1000), I want that Firm E is matched to Firm XYZ. So basically the iterative process should start from the smallest difference available. Consequently Firm ABC should be matched to D. Does that make sense and is it possible to implement that? 

[mceleavey]

Hi @DanMax ,

you can just change the calculation in my iterative macro. (right click, open macro)

Currently, it's just assigning the lowest Control Firm total assets. Create the difference calculation and select the lowest of that instead.

M