Join/Index Match with one source data file

[mhn423]

Hello -

I have an Excel table (columns 1 to 4) for which I used Index/Match to extract data (column 5) - e.g., INDEX($D...$D,Match(A2,$A...$A,0)). 

Company Level 1	Company Level 2	Company Level 3	Node Description	Result Desired
A11			Europe	Europe
A11	499		Europe	Europe
A11		401	Company Parent Europe, Limited	Europe
A11		403	Company Parent Europe, Limited 2	Europe
A11		407	Company Technology Company Limited	Europe
A11		408	Company Technology Company Limited 2	Europe
A11		411	Company Parent UK, Ltd.	Europe
A11		413	Company Parent UK, Ltd.	Europe
A11		414	UK EU EXP	Europe
B22			ROW	ROW
B22	300		LATAM	ROW
B22		301	Company Parent Mexico S. de R.L. de C.V	ROW
B22		302	zzzz-Company Parent Mexico S. de R.L. de C.V	ROW
B22		311	Company Brasil Solucoes de Pagamento Ltda.	ROW
B22		312	zzzz-Company Brasil Solucoes de Pagamento Ltda.	ROW
B22		321	Company Mexico, S. de R.L. de C.V	ROW

I am trying to replicate this in my Alteryx flow with Join (worfklow attached) and I am getting stumped. The results that I am getting replicates column A values for each change in column 4 (see attached results file).

What can I do in the Alteryx flow to get the results that I am looking for?

Thank you.

[Qiu]

I think maybe a multi-row formula will get the result we need here.

[mhn423]

Thanks for your reply! 

I actually don't want column A values to be replicated for each change in column D values. Right now, I have 16 rows of data, and I want to keep the 16 rows of data. I just want to add an additional column to lookup column A values and return column D values.

Maybe the solution is still with the Mulit-Row formula, I just don't know how to configure it so that my 16 rows of data doesn't turn into 130 rows of data.

[Qiu]

@mhn423 

Since its only 16 rows data, maybe you can give us a sample input and desired output to help us better understand the issue here? 😀

[DawnDuong]

hi @mhn423 

One thing i noticed is that the input only has 16 rows but the J output contains 130 rows. This is a red flag that the join condition is not unique.

A common mistake for new users of Alteryx when they use JOIN tool is that they forget to check that at least one of the JOIN condition must be "unique" otherwise you will get unexpected results of multiple rows of the same thing. 

In this case, in the .yxmd file, the JOIN condition is both on "Company level 1" for both L and R inputs and the field "Company level 1" does not uniquely identify a record in the data. That's why you have 130 join outputs when both L and R inputs only contain 16 records.

The attached excel does not contain the formula link, so I could not exactly follow the logic of how column E is supposed to be calculated. If you can re-upload the file with the formula, the community members likely can help you faster.

Cheers,
Dawn.

[mhn423]

Thanks both for your help. Here is the Excel input file (sorry, I thought I had attached it when I posted my question.)

The data is in column A to D, and the desired output is in column E, with the index/match formula. I will also check my workflow to see how I can make the join condition unique.

Thanks

[kelly_gilbert · Accepted solution]

@mhn423 - I think you could use the Sample tool for this purpose.

In Excel, MATCH will return the row number for the first matching row. In your example, there are lots of rows that match to "A11" in column A, so it is returning the first row, since that is the first row that matched to "A11".

The equivalent in Alteryx would be using the Sample tool to return the first row for each Company Level 1:

To @DawnDuong 's point, you want to be careful about the level of granularity on each side of your join. In this case, we know that the Sample tool will always output one row per Company Level 1, so you can then join back to the main data on Company Level 1 without getting duplicates.

[mhn423]

Thanks so much @kelly_gilbert , that worked well as a solution!

Mimi