Is this a SQL or Tableau question?
Tableau. The file is in Excel and I want to open it in tableau and join the worksheets. One worksheet has the information where the state with comma delimited is and the other has the information with state and region.
You can join them like you join any two tables but you won't get any matches as there are no matching values. Using SQL you could do a join in the where clause using like, however it's my understanding that Tableau does not support custom SQL when using Excel datasources. I don't believe what you are trying to do is possible with your data environment. You could try doing a lookup in Excel and using that as your data source.
I'm confused, why are there commas in Table A? Is this a CSV that needs to be read into Tableau with each state being its own field (PA | NY | MA | WV | VA | etc.)? My first thought is that the data needs a bit of cleanup prior to being loaded into Tableau.
Ok, let me clarify. The file comes from a website and it is in Excel. The file is not a csv file, it is an Excel file with 2 worksheets
1 worksheet has a list of projects and in one of the columns on the worksheet it has the states listed for the projects with the states separated by commas (WORKSHEET A). The other worksheet has the list of states and regions they belong to (WORKSHEET B). I need to be able to match each state on the comma separated column in worksheet A with the corresponding region in worksheet B
WORKSHEET A WORKSHEET B
Projects State(s) State Name State Region
Project A AR,AL,CA Alabama AL Southeast
Project B CA Arizona AZ Western
Project C AL,AR Arkansas AR Southwest
Project D AR California CA Western
NEED to be able to associate states in project A with the corresponding regions in worksheet B
Thanks for that clarification Gloria. With data in its current state, you will not be able to join on these two worksheets and get the results you're looking for. While both worksheets have a field called STATE, the definition of what goes in the STATE field is different for each worksheet. In Worksheet A, it's whatever state(s) the project is in, in worksheet B, it's whatever the 2 character abbreviation of the state. If you join these two sheets, the only "match" in that join, is places where projects are only in one state, such as California in your example.
Check out this tutorial on data blending: Data Blending and Data Integration It really helped me with blends in Tableau
This article on Joins is also a pretty good resource: http://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.htm#joining_tables.html
Joshua, Your example is exactly what I need. I just need to know which region they belong to.
1 of 1 people found this helpful
You could try the multiple consolidation technique in excel to break apart your projects and states.
First off, you will need to use Text to Columns to spread out your states (assuming that Projects and State(s) are the only columns in Table A)
Next, use the Pivot Table wizard only available through [Alt + D] [P]
Select Multiple consolidation ranges, click Next > , click Next > ,
Here you will need to use the cell selector to select you entire range (i.e. [Ctrl + Home] [Ctrl + Shift + End] then [Enter])
Click Add, Click Next > , New Worksheet, Click Finish
Now we have a Pivot Table (which we care nothing about because Pivot Tables do not show text in the Value areas)
However that said, double-click the where the Grand Totals meet:
Now we get a new table that holds all of the information that fed the Pivot Table.
You can delete the Column and Page 1 columns and delete out the blank rows in the Value Column, then do a vlookup for the regions in Table B.
And now you can use Tableau to explore your new data. Also if you have more columns with superficial information about each project, you should be able to add those columns back in, though you will have duplicates.
That is certainly a creative approach and it worked. Thanks for your assistance.