Two thoughts you might try...
Idea 1: Can you take a list of states and make regions using Custom Territories in Tableau and save that as an extract and use that for the join? (I haven't tried this, but figure it might work...)
Idea 2: If the regions don't change, and idea 1 doesn't work, you could create the regions in a GIS as separate polygon shapefile and join that into your data. Then you wouldn't have the row duplication problem. If you have a list of which states you have in which region it should be easy to do that by using a dissolve in QGIS (or whatever GIS you choose)
I'm happy to try these out if you can share a table with all of your state/regions (unless the only locations you use are the two regions in sample TWBX, but I figured that maybe you have more regions than that?)
Hmmm yeah idea 2 sounds interesting, I've never done anything like that before.
Right, I didn't list out everything in my example, but I'll attach the actual state to region key I've been using.
I'm not familiar with GIS, what is that?
State to Region Key 4.xlsx 12.3 KB
Just googled it, tried to download QGIS but I lack admin rights to install (locked down on my work laptop). I could maybe take a stab at it on my personal laptop, how hard is QGIS to learn for doing something like you stated?
Thank you for the list of states/regions! I'll see if I can just knock out making that region file for you... I'm going to guess 5 minutes in QGIS, but I have to find or download a states file first which will take 4 of those five minutes (because I have horrible file management skills )
As for learning QGIS... it depends on what you need to do. This type of task should be super quick once you have the data to start with and there are often TONS of tutorials online with step-by-step instructions on how to do dissolves, merges, etc.
If you think this is something you'd do with other files I can probably write up something simple and share.
Hopefully I'll have a new file for you in just a few min
Wow thanks so much! I really appreciate it. I doubt I'll come across this again (at least I hope not!) so if you are able to make that file then I think I would be set. Thanks again!
Okay, try joining your data with the attached region shapefile and let me know if that works for you...
Edit: And FWIW, your regions seem to have the same definition as the normal US Census regions so there may just be a region-based shapefile that you can download from their US Boundary files site.
Edit: Here is the link to the Census data to download regions:
Scroll down and you'll find the regions at three different scales:
cb_2018_us_region_500k.zip [2.2 MB]
cb_2018_us_region_5m.zip [<1.0 MB]
cb_2018_us_region_20m.zip [<1.0 MB]
region.zip 776.9 KB
Thanks! So it looks like I just bring it in as a 'spatial' data source correct? I joined the attached one to my existing data feed and it seemed to join ok, but then when I went into my workbook all of my sheets and dashboards were blank?
Trying the cb_2018_us_region_500k file now
edit: Ok that file loaded and joined ok but none of the fields in it were of geographic data type?
I re-tried the one you had as an attachment and it had a field 'state' that was geographic and I made the region field geographic data type off of state...but all values for the file's state and region were 'null'.
Guessing I must be doing something wrong. When I load these, should I not be choosing 'spatial' as the file type to load?
Ok I figured it out, great work Sarah! Your solution was exactly what I needed
I'm glad to hear that it worked out!