1 2 Previous Next 18 Replies Latest reply on Mar 16, 2016 3:11 AM by Allen Gapara

    Need help combine 9 columns into 1 without concatenating

    Allen Gapara

      I have the following columns from database

       

      Q1Value Q1Q2 Q2 valueQ3Q3 valueQ4Value Q4Q5Q6 valueQ7Q7 valueQ8Q8 valueQ9Q9 value

       

      and I would like Tableau to bring this view, when I put Question and Value on rows.

       

       

      QuestionValue
      Q12.3
      Q28.4
      Q35.6
      Q42.6
      Q58.7
      Q67.6
      Q78.3
      Q89.0
      Q9

      9.7

       

      and also different question comments for to come under comment.

       

      Comment1 Comment2Comment3Comment4Comment5Comment6Comment7Comment8Comment9

       

       

      Comment
      Comment1
      Comment2
      Comment3
      Comment4
      Comment5
      Comment6
      Comment7
      Comment8
      Comment9

       

      I am struck and have ran out of ideas. Thanks for your help.

        • 1. Re: Need help combine 9 columns into 1 without concatenating
          Corey Turner

          Allen,

           

          You can achieve this by putting your data in excel and Pivoting Q1-Q9 fields. (In the data connect / preview window, select Q1-Q9 fields -> right click -> Pivot.)

           

          If you do not want to connect to excel, you will have to normalize your data structure in the database or write custom sql. Hope this helps.

           

          Corey

          • 2. Re: Need help combine 9 columns into 1 without concatenating
            Steve Martin

            Allen,

             

            Cory is correct in advising that this data needs to be pivoted.

            Please can I ask what is your data-source? I ask as I am wondering whether the source is a database, and then if you are using SQL Server as SQL Server has pivot functions which would greatly help you here, whereas other tools, this would need to be a much bigger union all query.

             

            Steve

            • 3. Re: Need help combine 9 columns into 1 without concatenating
              Allen Gapara

              I can only view the database. I will be connected to a live source, and I understand Tableau does not like custom sql. The live database view cancels out  pivoting in excel.

              • 4. Re: Need help combine 9 columns into 1 without concatenating
                Steve Martin

                Allen,

                 

                Can you explain your comment "Tableau does not like custom SQL"

                 

                The majority of my workbooks (and this is a lot) uses custom sql. The major point you need to be aware of when working with custom sql is that Tableau will treat this source as a view and so shall require (unlike a view) for the complete resultset before it begins to work on creating filters and preparing its source.

                 

                Using highly optimised queries you can use CustomSQL without pain - most of my workbooks sit atop 2Bn row data-sets and still render in around 7 seconds simply by the level of work I am putting in in the first instance.

                 

                Steve

                • 5. Re: Need help combine 9 columns into 1 without concatenating
                  Allen Gapara

                  On many forums they say "Custom SQL connections are inefficient" and  Blended calculations can cause slow performance.

                  • 6. Re: Need help combine 9 columns into 1 without concatenating
                    Corey Turner

                    I agree with Steve...

                     

                    Custom SQL can be very effective for situations like this. Having just view access to your DB you can still write a query that pivots the data for you.

                    • 7. Re: Need help combine 9 columns into 1 without concatenating
                      Corey Turner

                      That is a generalization. When you write Custom SQL, Tableau cannot optimize the query for best performance, but if the query isn't overly complex it should be performant enough.

                       

                      Same for blended calculations. They have limitations and usually slow things down, but sometimes you can leverage data blending to speed things up! It's pretty complicated and depends on a lot.

                      • 8. Re: Need help combine 9 columns into 1 without concatenating
                        Allen Gapara

                        Any ideas, on how to write the custom SQL on pivoting.

                        • 9. Re: Need help combine 9 columns into 1 without concatenating
                          Corey Turner

                          Like Steve said, that will depend on your data source... What DB server are you using?

                          • 10. Re: Need help combine 9 columns into 1 without concatenating
                            Steve Martin

                            I have a very good idea on pivoting but need to know which server provider you are using so we can get the syntax correct and leverage the best performance available from using available functions.

                             

                            For obvious reasons, we cannot see your table structure though it will help to know the data-types that are being queried and the cardinality and whether any of the columns you require for your dataset are indexed.

                             

                            Also, if the table is partitioned and the number of records the table holds will be useful.

                             

                            For security reasons, it will be best if you mask the true column names

                             

                            Steve

                            1 of 1 people found this helpful
                            • 11. Re: Need help combine 9 columns into 1 without concatenating
                              Allen Gapara

                              T-SQL. I should have said SQL Server

                              • 12. Re: Need help combine 9 columns into 1 without concatenating
                                Steve Martin

                                Oh well, thats easy then as you can use the pivot function.

                                 

                                You haven't explained the columns you need and the rest of my request above. In the absence of this, I can only assume you have issues with supplying such information so I shall just direct you to the technet for pivotting:

                                Using PIVOT and UNPIVOT

                                 

                                Steve

                                • 13. Re: Need help combine 9 columns into 1 without concatenating
                                  Allen Gapara

                                  I have the following tables with an outer join

                                  SELECT [response_id]

                                        ,[date_at_venue]

                                        ,[timestamp]

                                        ,[response_platform]

                                        ,[person_full_name]

                                        ,[response_email]

                                        ,[person_postal_line1]

                                        ,[person_postal_line2]

                                        ,[person_postal_town]

                                        ,[person_postal_postcode]

                                        ,[response_mobile]

                                        ,[response_gender]

                                        ,[amount_name]

                                        ,[response_q1]

                                        ,[comment_q1]

                                        ,[response_q2]

                                        ,[comment_q2]

                                        ,[response_q3]

                                        ,[comment_q3]

                                        ,[response_q4]

                                        ,[comment_q4]

                                        ,[response_q5]

                                        ,[comment_q5]

                                        ,[response_q6]

                                        ,[comment_q6]

                                        ,[response_q7]

                                        ,[comment_q7]

                                        ,[response_q8]

                                        ,[comment_q8]

                                        ,[response_q9]

                                        ,[comment_q9]

                                        ,[comment_q10]

                                        ,[comment_q11]

                                        ,[comment_q12]

                                        ,[response_third_party]

                                        ,[response_phone_opt]

                                        ,[response_email_opt]

                                        ,[venue_name]

                                        ,[venue_ref]

                                        ,[response_source]

                                        ,[response_venue]

                                        ,[venue_region_id]

                                        ,[venue_region_name]

                                    FROM[dbo].[view_nps_responses]

                                   

                                  SELECT [nps_customer]

                                        ,[nps_customer_venue_id]

                                        ,[nps_customer_timestamp]

                                        ,[nps_customer_gender]

                                        ,[nps_customer_visit_freq]

                                        ,[nps_customer_question_one]

                                        ,[nps_customer_question_two]

                                        ,[nps_customer_question_three]

                                        ,[nps_customer_question_four]

                                        ,[nps_customer_question_five]

                                        ,[nps_customer_question_six]

                                        ,[nps_customer_question_seven]

                                        ,[nps_customer_question_eight]

                                        ,[nps_customer_question_nine]

                                        ,[nps_customer_platform]

                                        ,[customer_comments_question_one_comments]

                                        ,[customer_comments_question_two_comments]

                                        ,[customer_comments_question_three_comments]

                                        ,[customer_comments_question_four_comments]

                                        ,[customer_comments_question_five_comments]

                                        ,[customer_comments_question_six_comments]

                                        ,[customer_comments_question_seven_comments]

                                        ,[customer_comments_question_eight_comments]

                                        ,[customer_comments_question_nine_comments]

                                        ,[customer_comments_question_ten_comments]

                                        ,[customer_comments_question_eleven_comments]

                                        ,[customer_comments_question_twelve_comments]

                                        ,[nps_customer_date]

                                    FROM [dbo].[view_nps_response_feed]

                                  I would like the following tables with condensed question, comments, qcomments and response.

                                   

                                   

                                  SELECT [response_id]

                                        ,[date_at_venue]

                                        ,[timestamp]

                                        ,[response_platform]

                                        ,[person_full_name]

                                        ,[response_email]

                                        ,[person_postal_line1]

                                        ,[person_postal_line2]

                                        ,[person_postal_town]

                                        ,[person_postal_postcode]

                                        ,[response_mobile]

                                        ,[response_gender]

                                        ,[amount_name]

                                        ,[response_q]

                                             ,[comment_q]

                                             ,[response_third_party]

                                        ,[response_phone_opt]

                                        ,[response_email_opt]

                                        ,[venue_name]

                                        ,[venue_ref]

                                        ,[response_source]

                                        ,[response_venue]

                                        ,[venue_region_id]

                                        ,[venue_region_name]

                                    FROM[dbo].[view_nps_responses]

                                   

                                  SELECT [nps_customer]

                                        ,[nps_customer_venue_id]

                                        ,[nps_customer_timestamp]

                                        ,[nps_customer_gender]

                                        ,[nps_customer_visit_freq]

                                  ,[nps_customer_platform]

                                        ,[nps_customer_questions]

                                                 ,[customer_qcomments]

                                        ,[nps_customer_date]

                                    FROM [dbo].[view_nps_response_feed]

                                   

                                  Thanks for your help.

                                  • 14. Re: Need help combine 9 columns into 1 without concatenating
                                    Steve Martin

                                    So the way forward here would be drop the result set from these tables to a temp table or cte.

                                     

                                    In order to help you with your pivot syntax, I first need to know the relationship of these two tables (how do they join). Secondly, you need to define the pivot table specification ie how you would build your pt.

                                     

                                    It will be far easier if you could create this in Excel (use dummy data for the data fields) and then build your pivot table there this way I can see the exact spec required.

                                     

                                    Steve

                                    1 2 Previous Next