1 2 Previous Next 16 Replies Latest reply on Jun 14, 2016 6:11 PM by Christina Gremore

    Combining 2 columns into a single column

    Jared Torrence

      Hello,

       

      I have 2 columns in my data set that contain similar values.  The issue is that one column contains values for the left object while the other column contains the values for the right object.  I want to combine these 2 columns so that I can create a filter for users to select no matter whether it is the right or left object?  Any idea on how to automate this column combination?

       

      Jared

        • 1. Re: Combining 2 columns into a single column
          Paul Chaney

          Hey, Jared.  This depends on certain information I'm not sure we have yet.  If this is a case where the data is in only one of the two columns and the other is blank (null), you may be able to create a single calculated field that combines both columns, then use this result for your filtering field.

           

          If you have information in both columns in some cases and only want to use one of them based on some criteria, that would require some different logic.  If my first response doesn't fit your situation or if you want more thoughts on this, please add some more specifics and some brief sample data.  My guess is there's a fairly simple solution for this, it's just a matter of solving the right problem!  : )

           

           

          Paul

          • 2. Re: Combining 2 columns into a single column
            Christina Gremore

            Hi Jared,

            Could you share a little more about the structure of your data? Maybe a mock-up in two excel sheets?

             

            Off the top of my head, here are some things I might try:

            1. Combined field

            2. Concatenated string

            3. Editing the aliases for on of the columns so they match across the tables

            4. Doing some kind of filtering with a parameter

             

            We might be able to get a firmer idea of a solution with a little more info about the structure of the data, and what you're trying to achieve. Are the values for the left and right objects similar, or totally different?

            1 of 1 people found this helpful
            • 3. Re: Combining 2 columns into a single column
              Jared Torrence

              Hi Paul & Christina,

               

              Thanks for responding.  So my data looks like this:

              So the makeup of the data is the same, but an ID appears in one column or the other.  I want to consolidate all of them into a single column so that I can filter on IDs as a whole.

              • 4. Re: Combining 2 columns into a single column
                Paul Chaney

                This definitely looks like concatenation is a good option.  You can create a calculated field defined as "[Left ID]+[Right ID]"...or, I'd recommend using "TRIM([Left ID]+[Right ID])" to ensure any leading and trailing spaces are removed.  You will most likely need to change the data types to string in order for this to work correctly, although numerics may add together well enough (for example, Left ID of 0 + Right ID of 1234567 should result in 1234567).  Typically, you would not want to use ID fields as measures anyway, so string should be the desired data type.

                 

                I would recommend validating your data before each analysis (using a canned routine) to ensure there is only one value in these columns for each row of data.  This is a fairly quick test that can add value to the analysis.

                 

                 

                Paul

                • 5. Re: Combining 2 columns into a single column
                  Christina Gremore

                  If these are integers, and there is only ever a null and an integer for every row, you can do this calc:

                   

                  ZN(Left ID) + ZN(Right ID)

                   

                  If they are sometimes alphabetical characters, you can do this:

                   

                  TRIM(IFNULL(STR([Left ID])," ")+ IFNULL(STR([Right ID]," "))

                   

                  I really recommend doing the first operation if at all possible, because integers are processed faster than strings, so that will be a faster solution.

                   

                  Then you can just filter on that field.

                   

                  If this resolves your issue, please mark this as the correct answer.

                  3 of 3 people found this helpful
                  • 6. Re: Combining 2 columns into a single column
                    Christina Gremore

                    He'll need to account for the fact that the blank ones are coming in as nulls, or else all of the calcs will output "Null." My calcs show you how to do this.

                    1 of 1 people found this helpful
                    • 7. Re: Combining 2 columns into a single column
                      Paul Chaney

                      Great info, Christina.  I'm learning so much from you today!  I wondered if IFNULL needed to be considered for that reason.  Excellent.

                       

                       

                      Paul

                      • 8. Re: Combining 2 columns into a single column
                        Jared Torrence

                        Christina,

                         

                        After looking, I now see that some of my rows of data actually contain values in both left and right ID. Ex.

                        Left ID                Right ID

                        5132888               3128818

                         

                        This tactic would concatenate both together.  Any way to handle this situation?

                        1 of 1 people found this helpful
                        • 9. Re: Combining 2 columns into a single column
                          Paul Chaney

                          Christina will probably dazzle and amaze with her reply, no doubt...but, in the meantime, I'll put this thought out there.  You may need to explain more about what you're wanting to see in the final results, given this new info.  If it's an either/or situation (i.e., either Left ID or Right ID have data, but not both), that's fairly easy to think through.  But, with data in both places at times, I think we'll need to know which one (or both?) to use in certain cases.  Once we know that, I believe we could create the appropriate IF/THEN/ELSE logic in the calculated field.

                           

                           

                          Paul

                          • 10. Re: Combining 2 columns into a single column
                            Christina Gremore

                            Hm, this makes it a bit more complicated. Now we have to start thinking creatively!

                             

                            1. Are you 100% positive that these tables need to be joined, and not unioned? I only ask because this is something that comes up fairly frequently for new Tableau users, especially when they have this particular type of question where they need 2 columns from 2 separate tables to function as though they were 1 column.

                             

                            A quick primer on Unions, in case any potential readers aren't familiar: Say you have two tables that contain basically all of the same field names and number of fields, but represent different events or entities. You want to combine them into one table. A union allows you to "stack" two tables with the same number of fields and field types, essentially appending table 2 to the end of table 1. When the union is complete, all the field names are the same. Does this sound like what you need to do, Jared, or are you confident that the tables must be joined?

                             

                            2. Do your end users know what these IDs are? As in, can they just type the values in? If so, you can use the String concatenation formula* and then throw that on the filter card. When the filter dialog opens up, go to the Wildcard tab and select "Contains." Type in a value in the "Match Value" text box, and click OK. Now there will be a little blank text box on the side that they can type in whichever value they're looking for, and it will filter directly.

                             

                            3. If the end users don't know what the IDs are and must be able to browse, I'd need to know more about your use case and what kind of dashboard/visualization you're trying to create. Perhaps there is a way to accomplish this with guided analytics / Action filters.

                             

                            * You'd need to make a slight modification to the string calc so it appears as follows:

                             

                            TRIM(IFNULL(STR([Left ID])," ") + " " + IFNULL(STR([Right ID])," "))

                            1 of 2 people found this helpful
                            • 11. Re: Combining 2 columns into a single column
                              Jared Torrence

                              Well these are just 2 columns that I'm looking to combine, but I think a union would be better.

                               

                              End users do know these IDs.  I think the search box would be helpful, but does it allow users to select multiple IDs?  That's the real goal is for them to be able to filter by both left & right IDs.

                              • 12. Re: Combining 2 columns into a single column
                                Christina Gremore

                                It's very important to get the data structure right. If you have basically all the same column headers in both tables, then you'll want to union these tables. You can investigate what unioning does here. If you're connecting to a flat file (Excel, .txt, CSV), you can perform that Union in the Data Connection window of Tableau. If you're connecting to a relational database, you should consider creating a view, or using Custom SQL to perform that union.

                                1 of 1 people found this helpful
                                • 13. Re: Combining 2 columns into a single column
                                  Jared Torrence

                                  I'm currently utilizing a view on top of a relational database and Left & Right IDs are 2 columns within a single table.  So I don't believe a union would benefit me since everything is already in a single table.

                                   

                                  Hoping a method using a calculated field within Tableau will solve this.

                                  • 14. Re: Combining 2 columns into a single column
                                    Christina Gremore

                                    Ah, interesting. OK, then this is what you'd want to do:

                                     

                                    1. Create the string concatenation calc I showed earlier

                                    2. Put it on the filter shelf

                                    3. On the Filter card that shows up, click the drop-down arrow in the upper right corner, and select Multiple Values (Custom List). They can type in the values they want, and will be able to select multiple values at once.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next