Unique tool without excluding dupliactes

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

Preparation

Accepted answers

AngelosPachis

Hi @DanMax ,

I consider that to be possible in all workflows but each one of them will have a different tweak. The key here would not be to make all of them work of course, but understand more or less how they work as you might need that knowledge going forward.

In the workflow I have attached in my previous post, all you need to do is open the iterative macro and change the sort tool so you only sort on the Difference field and then save the new iterative macro.

That will match XYZ with E and in the next run ABC will match with D.

Hope that makes sense in a way, let me know if there are any questions.

Cheers,

Angelos

mceleavey

@DanMax ,

I've attached the new macro using the difference to drive the first selection.

This gives the following:

Let me know if this is correct.

DanMax Iterative Macro.yxmc

All comments

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.

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.

New Workflow14.yxzp

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.

DanMax Iterative Macro.yxmc

mceleavey_solution.yxzp

mceleavey_solution.yxmd

AngelosPachis

@DanMax

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

Output

example.yxzp

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.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177