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.
Solved! Go to Solution.
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.
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? 😀
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.
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
@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.