10 Replies Latest reply on Feb 9, 2016 10:10 AM by Gloria Van Arsdale

    How do I join comma delimited column with a table?

    Gloria Van Arsdale

      I have a table (TABLE A) that has a column called states with the states listed and separated by commas.  I want to join each state on that list with a table (TABLE B)  that has a list of states and the region they belong to.   I need to find out how to join them.

       

      TABLE A

      State(s)

      PA,NY,MA

      WV,VA,NC

      WA,OR

      WV,VA,NC

      OH,TN,KY,LA

      NY,CT,MA

      OH,WV,VA

      OH,WV,PA,NJ

      NJ,NY,CT,RI,MA

       

      TABLE B

      State Name         State      Region

      Alabama              AL          Southeast

      Arizona                AZ          Western

      Arkansas            AR          Southwest

      California            CA          Western

        • 1. Re: How do I join comma delimited column with a table?
          Ivan Young

          Is this a SQL or Tableau question?

          • 2. Re: How do I join comma delimited column with a table?
            Gloria Van Arsdale

            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.

             

            • 3. Re: How do I join comma delimited column with a table?
              Ivan Young

              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.

              • 4. Re: How do I join comma delimited column with a table?
                Nathan Panuco

                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.

                • 5. Re: How do I join comma delimited column with a table?
                  Gloria Van Arsdale

                  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

                  • 6. Re: How do I join comma delimited column with a table?
                    Nathan Panuco

                    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

                    • 7. Re: How do I join comma delimited column with a table?
                      Joshua Cloud

                      Gloria,

                       

                      If I am understanding correctly, you don't necessarily need the regions per each project back into a comma delimited form, correct? Just being able to identify the regions associated with each project would be enough?

                       

                      Like below perhaps?

                       

                      Regards,

                      • 8. Re: How do I join comma delimited column with a table?
                        Gloria Van Arsdale

                        Joshua,  Your example is exactly what I need.  I just need to know which region they belong to.

                        • 9. Re: How do I join comma delimited column with a table?
                          Joshua Cloud

                          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.

                          Regards,

                          1 of 1 people found this helpful
                          • 10. Re: How do I join comma delimited column with a table?
                            Gloria Van Arsdale

                            That is certainly a creative approach and it worked.  Thanks for your assistance.