10 Replies Latest reply on May 3, 2013 7:40 AM by Olga Giannoudi

    Connecting 3 excel datasheets-inconsistent mapping of the common categories

    Olga Giannoudi

      Hello everyone, I am new in the world of tableau and if it weren't this site I would be more lost than I really am.

      I have a big task for my company  and I really need help.

      I have 3 big excel data sheets.

      Each of them has different measures and values  by quarter ( starting Q1 2011-Q4 2012)

      First, I have difficulty in making the data consistent because the categories the first excel sheet uses are not exactly the same with the the others

      For example: The first source might have data for Shampoo  and the other Shampoo 2in 1 or Shampoo+ Conditioner

      How can I blend this data? Should I  do it in excel first? Is there a way to make filters and for every measure I want to give me a drop down menu and choose the category I want?

      It would be good to use parameters maybe?

       

      Also, the most important thing is that I don't know how to connect the measures from the three datasources I have. If I blend these data the secondary source is not recognised and returns a null or *.

       

      Is there a way to connect all three excel sheets into one and then use this super-sheet to do my analysis?

       

      I hope it is not too complicated

       

      I would appreciate a response and an insightful approach

       

      Many thanks

      Olga

        • 1. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
          Dimitri.B

          I would recommend joining the three sheets in Tableau connection setup. The categories don't have to have the same names, as long as you know what matches what.

          When making/editing your connection, just use Multiple Tables option to add other sheets and select what fields to join them on.

           

          add tables.png

           

          join dialog.png

          1 of 1 people found this helpful
          • 2. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
            Olga Giannoudi

            Thank you very much for the info!

            I did this and I joined the fields I want, but  there is a field in all excel sheets that is called measures.

            I don't want to join this, simply I want a field that will aggregate and add the content of the specif fields called measure.

             

            For example: Measure field in the first sheet is: Value share and Average Price Index ( there is also a specific field with the values for this)

             

            Measure in the other sheet: Penetration and Loyalty

             

            I have joined the fields that are the same ( like country, category..) but I need these measures to appear all together in a field ( not to have two fields with the same name)

            I hope you understand my point!!!!

            I really appreciate your help!

            Many thanks

            Olga

            • 3. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
              Alex Kerin

              It sounds like you may need to use custom SQL to union all the sheets together.

               

              Without seeing your Excel sheet (even if data is removed) and what you want as a result, it will be hard to help you

              1 of 1 people found this helpful
              • 4. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
                Olga Giannoudi

                Hello Alex

                Thank you for the reply.

                Well I am quite new to tableau and to be honest I have never used SQL before ( just started watching some videos but that's all)

                Please find attached the excel book I made for you. There are massive sheets with many data. I kept the numbers off though.

                In all three sheets there are same data fields.

                The specific measures in each spreadsheet are different.

                I want to combine all the measures I have from the sheets and get the data to tableau and get a dynamic view of wat is in: Final Result Tab.

                Also in this tab you can see the legend down, I put randomly some data and the arrow appeared with the proper sign.

                From my point of view,

                In order to help myself dealing with these data I created the supercategory column in the second tab ( in red) because as you can see it exists in my other tabs as well.

                The most difficult part for me is to combine all this data because I have slighty different names or more expanded categories in one sheet and not in another. In the first tabs you can see I have two columns for brand and global brand, whilst in the third tab I have the brand position type ( global/ local)

                 

                If there is a way to manage them in tableau it would be great otherwise I will have to do it in excel?

                Also the most important is how to link the different measures I have from the sheets

                If I manage to join everything and then to built it in a way that I will have a drop down menu to choose the brand and the category and then to have all the values for the measures ( to fill the final result tab) it would be useful.

                 

                Any ideas? I am really nervous about this, because I don't know exactly what my steps should be.

                Thank you for the help

                Olga

                • 5. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
                  Alex Kerin

                  Okay, you need to decide what the mapping is between the sheets. How does net category map to gender category to super category, if at all. Then decide what you want the other sheets to show if there is a column you don't have (third sheet has no 2nd category column).

                   

                  You also really need to reshape the dates so that you have a single column with just dates. See here: http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis There is an Excel tool that can help you do this. If you don't do this, the initial load will be very painful, and updating the file with new quarters equally as time consuming. With that said, if this is the format that the data comes in as, then you will have to reshape the data every time anyway - is there the possibility of changing the way the data is sent to you?

                   

                  After all of that the process is pretty straightforward. You can connect to each of the sheets, get the SQL used and manipulate this to get one big file, and union all them together (http://kb.tableausoftware.com/articles/knowledgebase/union-related-data-across-multiple-excel-tabs). There will be some extra work for the missing columns, but nothing insurmountable.

                   

                  This comes down to how often you are doing this. If it's a one off, I'd pull it all together in Excel. If this is often repeated I would persevere in Tableau, but those date fields are going to be an issue.

                  1 of 1 people found this helpful
                  • 6. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
                    Olga Giannoudi

                    Thank you Alex

                    Actually I have a  VBA macro that I used and managed to put all quarters in one column, so I named it Data periods and I have the values for that.

                    Regarding the measures, the SQL will help me?

                    Many thanks to your help!

                    Olga

                    • 7. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
                      Shawn Wallwork

                      A veritable tome; what happened to short and pithy?

                       

                      --Shawn

                      • 8. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
                        Olga Giannoudi

                        Thank you Alex

                        I have all the dates in a column now and have a separate column for values.

                         

                        Regarding what you told me: ''You can connect to each of the sheets, get the SQL used and manipulate this to get one big file, and union all them together''

                         

                        Please can you give me an example on how to apply the custom sql to each sheet? How should I start? Connect to each sheet ? If I do that how can I link them all together?After that I have to use UNION ALL?

                        I am quite lost in numbers and I tried to work on some sql based on the exaple you gave me but it is not working...

                        Any insightful feedback?

                        Many thanks

                        Olga

                        • 9. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
                          Alex Kerin

                          Okay, good work. Assuming you now have a column called [data period] on each sheet, and another called [category], and so on so that they are common (you don't absolutely need to do this, but it makes it easier), we grab the SQL that would be used to connect to each sheet individually (right click the data source, edit connection, choose the custom SQL radio button):

                           

                          SELECT [First$].[Brand] AS [Brand],

                            [First$].[Category] AS [Category],

                            [First$].[Country] AS [Country],

                            [First$].[Data Periods] AS [Data Periods],

                            [First$].[Global Brand] AS [Global Brand],

                            [First$].[Measure] AS [Measure],

                            [First$].[Net category] AS [Net category]

                          FROM [First$]

                           

                          Ideally each sheet would have only the columns needed and  the same column names. They don't have to, but you would need to edit the SQL as appropriate. However, whatever you end up with, each part of the union all must have the same columns named in Tableau (after the AS) and the same number. When done, we can create one master sheet:

                           

                          SELECT [First$].[Brand] AS [Brand],

                            [First$].[Category] AS [Category],

                            [First$].[Country] AS [Country],

                            [First$].[Data Periods] AS [Data Periods],

                            [First$].[Global Brand] AS [Global Brand],

                            [First$].[Measure] AS [Measure],

                            [First$].[Net category] AS [Net category]

                          FROM [First$]

                          union all

                          SELECT [Second$].[Brand] AS [Brand],

                            [Second$].[Category] AS [Category],

                            [Second$].[Country] AS [Country],

                            [Second$].[Data Periods] AS [Data Periods],

                            [Second$].[Global Brand] AS [Global Brand],

                            [Second$].[Measure] AS [Measure],

                            [Second$].[Net category] AS [Net category]

                          FROM [Second$]

                          union all

                          SELECT [Third$].[Brand] AS [Brand],

                            [Third$].[Category] AS [Category],

                            [Third$].[Country] AS [Country],

                            [Third$].[Data Periods] AS [Data Periods],

                            [Third$].[Global Brand] AS [Global Brand],

                            [Third$].[Measure] AS [Measure],

                            [Third$].[Net category] AS [Net category]

                          FROM [Third$]

                           

                          If you can share your new file with the columns, I can help you create the SQL for it.

                          • 10. Re: Connecting 3 excel datasheets-inconsistent mapping of the common categories
                            Olga Giannoudi

                            Alex,

                            I did what you told me. I made sure that the names of the columns in each sheet are the same.

                            I picked data only for two countries and applied the formula you sent me.

                            It is ok now, but if you see the image I will send you. The exclamation  mark in two dimensions says that this field does not exist in the database.

                            This field is the country. This is weird because the field country exists in all my sheets. Apart from that the measures from second and third tab when I choose the country I want appear in tableau, while the ! exists.

                            What is that?

                            My intention was to connect all data in tableau without having to do the cleaning in excel, because it is time consuming.

                            What matters is to get them there. So I focused only on a couple of countries and then I will try to apply it in all of them.

                            Please have a look and get back to me when possible!!