11 Replies Latest reply on May 18, 2017 12:07 PM by Justin Larson

    Joining Multiple Columns of Data

    kyle.besley

      Hi All,

       

      I am currently conducting a mandatory anonymous survey with thousands of participants and importing the data into tableau using a WDC.  I have a master file of all of the surveys to be conducted on each particular place, etc.   I have questions within the survey asking this demographic data - with the assumption that I can join the information to the master list to understand how many are left to be completed (and which ones).

       

      The survey platform I am using required that I split this demographic information into multiple, identical questions (which I used logic to direct participants to the correct set for them based on questions answered previously).  So for instance, I have one question asking which country and state they are in, and based on that they are taken to one of 10+ identical questions asking the participant to use a drop downs to indicate their city, company name, etc.  The issue I am running into now...is that I want to join the single master data list to 10+ of these columns within the data....is there a way to join based on calculated fields I created within tableau to consolidate the columns? Or any way to create a join calculation to do this?  Any help would be appreciated - keep in mind I have a WDC and require live data for this issue, so the 10+ columns are constantly being updated with new data (thus I can't export to excel and consolidate).  I have attached a very basic tableau workbook that I think illustrates the problem: trying to joing set 1,2,3 to the master file simultaneously

        • 1. Re: Joining Multiple Columns of Data
          Justin Larson

          Need clarification to approach an answer.

           

          How many datasets are you looking at here?

               reading description, sounds like you have 10+ data sets you are trying to join, but looking at workbook, there are two, one with Master list of keys, and second with 3 separate mutually exclusive key columns

           

          are you essentially trying to join two tables where

              table1.key = table2.key1

          OR table1.key = table2.key1

          OR table1.key = table2.key2

          • 2. Re: Joining Multiple Columns of Data
            kyle.besley

            Hi Justin,

             

            Thanks for the response.  There are 2 data sources: 1) a file containing all of the data to be collected; 2) a WDC of data collected.  In an ideal world, the 3 key columns in the WDC file would be a single column that I could join to the master data.  Sorry if this is a poor explanation.

            • 3. Re: Joining Multiple Columns of Data
              Justin Larson

              ok, if I am understanding this correctly, your first file has a single column with a key that relates to one of three fields in your WDC data. Further, the of the 3 columns in your secondary table, only one would contain a value. What you'd like to do is combine the 3 columns into one using the non-null column's value, then join on that.

               

              If I'm on the right path, this is quite straight forward. When you click on the drop down box to establish join criteria, the bottom options is "create join calculation". All you need to do is select that, and write a calculation that brings in the non-null value from one of those three columns. There are lots of syntaxes that would work, but for example:

               

              IFNULL([City1],IFNULL([City2],[City3]))

               

               

              Is this what you're after?

              • 4. Re: Joining Multiple Columns of Data
                kyle.besley

                Hi Justin,

                 

                Sorry for the delay in response....This is what I am looking for - could you help me understand how to expand this formula to more than 3 columns? Say city 4 and city 5 existed?  I tried but im still really novice.

                 

                Thanks!
                Kyle

                • 5. Re: Joining Multiple Columns of Data
                  Justin Larson

                  if you have more than three columns, and there is truly only one non-null, I would write a calculation on the joining logic on the WDC side that follows this pattern:

                  ifnull([city1],'') +

                  ifnull([city2],'') +

                  ifnull([city3],'') +

                  ifnull([city4],'')

                   

                  and add as many fields are you can stomach.

                   

                  Ifnull will return the field listed in the first argument unless it's null. If it's null, it will return the second instead. What I've done is for each field if it's null, I return an empty string instead, then just add them together (concatenating strings in Tableau is just 'String1' + 'String2' = 'String1String2') If you pass a null in any part of a string concatenation, the whole thing goes null, thus I replace nulls with an empty string.

                  • 6. Re: Joining Multiple Columns of Data
                    kyle.besley

                    This solution worked in test data, and was a valid formula in the real data - but I received these two errors, which I think have to do with the Web data connector.  The errors only happen when the formula is used to join the tables....any insight?

                     

                    • No such function IFNULL that takes arguments of type (str, str).
                    • Unable to create extract

                     

                     

                    Thanks!

                    Kyle

                    • 7. Re: Joining Multiple Columns of Data
                      Justin Larson

                      Hmm. Some formulas are not available in all data sources. This seems to be one of those cases. We'll have to try a few others until we find one that works.

                       

                      Try:

                       

                      if isnull([City1]) then '' else [City1] end +

                      if isnull([City2]) then '' else [City2] end +

                      if isnull([City3]) then '' else [City3] end +

                      if isnull([City4]) then '' else [City4] end

                       

                      Does the exact same thing. Just uglier to look at.

                      • 8. Re: Joining Multiple Columns of Data
                        kyle.besley

                        Oh thanks!  This time we got:

                         

                        • No such function + that takes arguments of type (str, str).
                        • Unable to create extract
                        • 9. Re: Joining Multiple Columns of Data
                          Justin Larson

                          I don't have any experience with WDC data sources, so I'm not sure what the allowed syntax is, but there must be a way to evaluate what is null.

                           

                          Joe Mako , I think I saw you have some experience with WDC. what's the legit isnull() syntax that is WDC-compatible?

                          • 10. Re: Joining Multiple Columns of Data
                            kyle.besley

                            Hi Justin Larson

                            Do you have any advice on where I can take this issue?

                            • 11. Re: Joining Multiple Columns of Data
                              Justin Larson

                              Wish I could give you more specifics, but with no one chiming in here, I'd just reach out to support directly. They are super responsive and should be able to hone in on exactly what you need to do. You can point them to this thread for details. If you have a licensed copy of Tableau, you've got support.

                               

                              Support & Services | Tableau Software