Hi Everyone,
I am joining 7 tables together and am encountering some duplication problems. I have created some simplified tables below to give an idea.
ID | Label_1 |
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
ID | Label_2 |
2 | F |
2 | G |
3 | H |
5 | I |
ID | Label_3 |
2 | J |
2 | K |
2 | L |
4 | M |
4 | N |
As you can see, every table has an ID column which is what I am joining on, however some tables have not got all 5 (25 in my actual data) of the IDs and some tables have more than one result for each ID. The result I am getting is as follows.
ID | Label_1 | Label_2 | Label_3 |
1 | A | NULL | NULL |
2 | B | F | J |
2 | B | G | J |
2 | B | F | K |
2 | B | G | K |
2 | B | F | L |
2 | B | G | L |
3 | C | H | NULL |
4 | D | NULL | M |
4 | D | NULL | N |
5 | E | I | NULL |
What I would like to get is
ID | Label_1 | Label_2 | Label_3 |
1 | A | ||
2 | B | F | J |
2 | G | K | |
2 | L | ||
3 | C | H | |
4 | D | M | |
4 | N | ||
5 | E | I |
I am happy for the ID column to stay as is or to not have duplicates, just as long as the Label columns are not duplicated.
Kind Regards,
James
Solved! Go to Solution.
The Tile tool is my favorite tool for use cases like this. Set the Tile tool to "Unique Value" and it works as a GroupBy RecordID tool. Use this to assign a RecordID (Tile_Num) for each unique value that occurs in each "Label_" field, and join on ID and the new RecordID (Tile_Num) field. Finally, a Multi Row Formula tool will fill in the missing ID values.
Check out the attached solution and let me know if you have any questions.
Worked perfectly!
Thanks a lot!
User | Count |
---|---|
60 | |
24 | |
24 | |
21 | |
21 |