There are many posts on joining data but i haven't been able to find one for exactly what i am trying to do. My two data sets have a many to many relationship and neither the join nor union can do what i want it to do. See below for an example of the data:
Input 1
Project ID | Milestone Date | Milestone |
1 | 10/1/20 | Milestone 1 |
1 | 10/15/20 | Milestone 2 |
1 | 10/31/20 | Milestone 3 |
Input 2
Project ID | Milestone Date | Milestone |
1 | 10/2/20 | Milestone 1a |
1 | 10/14/20 | Milestone 2a |
I need to join on Project ID but that would give me 6 rows of data. It is impossible to add the other columns of data to the join criteria as they are almost never named the same or use the same dates, but i would like to be able to group them together to compare the datasets so it would look like this (sometimes i have more records on the Left and sometimes i have more records on the Right)
Desired Output
Input 1 Project ID | Input 1 Milestone Date | Input 1 Milestone | Input 2 Project ID | Input 2 Milestone Date | Input 2 Milestone |
1 | 10/1/20 | Milestone 1 | 1 | 10/2/20 | Milestone 1a |
1 | 10/15/20 | Milestone 2 | 1 | 10/14/20 | Milestone 2a |
1 | 10/31/20 | Milestone 3 | Null | Null | Null |
Any Ideas on how to go about this?
Solved! Go to Solution.
Okay how about:
create a rowid per projectid. You can use my macro for that. https://gallery.alteryx.com/#!app/CReW-GroupByRecordID/5e417bbf0462d70decb763a9
now join on projectid and rowid. Union the results.
cheers,
mark
Oh wow, i can see now how that would work. Let me give that a shot.
Hi @MarqueeCrew
Is it possible for you to share your macro once more? The site you linked doesn't seem to have it anymore.
This will be really helpful for my use case.
Thank you!
Best regards,
Cal
Hi @Ryan_Myers
Could you share a sample of your workflow to see? If you have the macro, it would be great to share it here for future reference.
Thank you!
Best,
Cal
Never mind, I found something here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Record-ID-based-on-Grouping/td...
The Tile Tool does that! Thanks all.