I have data that looks similar to the following:
ID | Region | Name | Order Count | Type |
1003 | North | Bob Jones | 5 | Primary |
1004 | West | Jim Cobbler | 7 | Primary |
1004 | East | Jim Cobbler | 3 | Secondary |
1005 | North | Patricia Smith | 8 | Primary |
1005 | Central | Patricia Smith | 2 | Secondary |
I want to have a single row by ID where we would have a Primary Region with a total order count for the Primary Region and then a Secondary Region with a total order count for the Secondary Region. At most, there are only ever 2 rows by ID. So, there will always be either 1 or 2 rows per ID.
Want to end up with something like this:
ID | Name | Primary Region | Primary Region Order Count | Secondary Region | Secondary Region Order Count |
1003 | Bob Jones | North | 5 | Null | Null |
1004 | Jim Cobbler | West | 7 | East | 3 |
1005 | Patricia Smith | North | 8 | Central | 2 |
Solved! Go to Solution.
Thanks @binuacs , this worked well for me. I was trying to use the Transpose tool and Cross Tab tool, but this was a simpler method.
@arnettmh I added some extra data to try and show you how to future proof your work if you come across this scenario again.
You just need to keep adding another layer by adding another Filter tool iteratively.
Cool tip, to help with the renames, inside the options of join tools you can clear all renames, and then highlight the ones you want to add the prefix to i.e. Primary, Secondary, etc. Makes it so much easier for renaming.