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 1Company Level 2Company Level 3Node DescriptionResult DesiredA11 EuropeEuropeA11499 EuropeEuropeA11 401Company Parent Europe, LimitedEuropeA11 403Company Parent Europe, Limited 2EuropeA11 407Company Technology Company LimitedEuropeA11 408Company Technology Company Limited 2EuropeA11 411Company Parent UK, Ltd.EuropeA11 413Company Parent UK, Ltd. EuropeA11 414UK EU EXPEuropeB22 ROWROWB22300 LATAMROWB22 301Company Parent Mexico S. de R.L. de C.VROWB22 302zzzz-Company Parent Mexico S. de R.L. de C.V ROWB22 311Company Brasil Solucoes de Pagamento Ltda.ROWB22 312zzzz-Company Brasil Solucoes de Pagamento Ltda. ROWB22 321Company Mexico, S. de R.L. de C.VROW 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. Alteryx_Transformed_Company Level 1 Name.xlsx Alteryx_Index_Match_Q.yxmd

Join/Index Match with one source data file

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.

Alteryx_Transformed_Company Level 1 Name.xlsx

Alteryx_Index_Match_Q.yxmd

Workflow

Accepted answers

kelly_gilbert

@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.

Excel Index-Match - 2021-06-21.yxzp

All comments

Qiu

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

Alteryx_Index_Match_Q.yxzp

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

Alteryx_Index_Match_Q.xlsx

kelly_gilbert

Excel Index-Match - 2021-06-21.yxzp

mhn423

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

Mimi

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280