Hello. Good day everyone. Can I get advice on how I can get the results below. I tried find and replace but I only get 1 result. I used join tool as well but I wanted to show the result for DEF to be blank. Is there any way this is possible? Also, if there is a way to get the highest value in Column 5 on the Output table and being reduced back to the original Table 1. Thank you in advance! Would really appreciate anybody looking into this :)
Table 1
Column1 | Column2 |
ABC | 123 |
DEF | 456 |
GHI | 789 |
Table 2
Column1 | Column2 | Column3 |
ABC | 123 | 5 |
ABC | 159 | 6 |
ABC | 753 | 12 |
ABC | 456 | 3 |
ABC | 135 | 4 |
GHI | 147 | 7 |
GHI | 123 | 8 |
GHI | 159 | 9 |
GHI | 478 | 10 |
GHI | 236 | 45 |
Output
Column1 | Column2 | Column3 | Column4 | Column5 |
ABC | 123 | ABC | 123 | 5 |
ABC | 123 | ABC | 159 | 6 |
ABC | 123 | ABC | 753 | 12 |
ABC | 123 | ABC | 456 | 3 |
ABC | 123 | ABC | 135 | 4 |
DEF | 456 | |||
GHI | 789 | GHI | 147 | 7 |
GHI | 789 | GHI | 123 | 8 |
GHI | 789 | GHI | 159 | 9 |
GHI | 789 | GHI | 478 | 10 |
GHI | 789 | GHI | 236 | 45 |
Highest value in Column 5
Column1 | Column2 | Column3 | Column4 | Column5 |
ABC | 123 | ABC | 753 | 12 |
DEF | 456 | |||
GHI | 789 | GHI | 236 | 45 |
Solved! Go to Solution.
Use a Summarize tool on Table 2, grouping by Column 1 and 2 and getting the Max of Column 3. That can go into the Join then!
If you want to bring back the records that drop out, use a Union tool after your Join to bring them back.
Alternatively, you could use the Find Replace: VLookUps with Designer - Alteryx Community
Hope this helps - - input both tables - add a join (join column 1) in the join tool - rename right column 1 to Column 3, right column 2 to Column 4, right column 3 to Column 5 - add a union tool and union join and left output (left over from table 1) - add a sort tool Column 1 ascending and Column 5 descending - add a sample tool select first N rows with N = 1 and group by Column 1
@rchua another option