9 Replies Latest reply on May 21, 2017 10:48 PM by Andy Hsieh

    Connect two excel files

    Andy Hsieh

      Hi all,


      I learned Tableau for one semester when I was back in college. Nevertheless, I am using Tableau again for my work now, and I have a problem that I can not solve by myself.


      I have two excel files with me right now (excel 1 & excel 2), and both files contain a worksheet (excel 1 contains worksheet 1 & excel 2 contains worksheet 2) with "name" column (both worksheet 1 & worksheet 2 column A is "name" with same data).


      In Tableau 9.0, I have created worksheet 1 with excel 1 worksheet 1 and worksheet 2 with excel 2 worksheet 2. I have "name" as filters within both worksheets. And now, I am creating a dashboard that contains both charts from worksheet 1 and worksheet 2. However, the "name" filters only controls the data from their own worksheets instead of controlling both charts at the same time. Here is my question, how can I keep only one "name" filter within the dashboard (remove the other one) and make that filter controls both charts from different worksheets (different excel files)? On the other hand, how do I connect two different excel files within Tableau?


      I tried to use this 1.PNG to link fields, but it doesn't work.


      Thank you for your time!!


      Best regards,


        • 1. Re: Connect two excel files
          Warren Holroyd

          Can't remember the capabilities of Tableau 9, but I think the cross-datasource filtering may have been different.


          Do you see these options on your filter?


          I think it may have been a T10 addition that fleshed this out: As requested, you can filter across data sources in Tableau 10 | Tableau Software


          You'll probably have to blend the data in the viz if you're using T9x.


          Have a read of this: Quick Filter affecting multiple data sources


          Better yet, post a file.

          • 2. Re: Connect two excel files
            Andy Hsieh

            Thank you Warren,


            I only have 3 options within Apply to Worksheets, so I guess that All Using Related Data Sources is for T10 or above.



            I am currently looking for a solution for 9.0 because I am doing this for a baseball team, and they are using 9.0 on their laptop.


            I read about Quick Filter affecting multiple data sources , I think I have similar issue as his. I see how Richard solved the question by opening a new worksheet called Chooser, but I don't understand how he did it though.


            I did read some post about blending the data, but I have no idea how to do that either.


            I just learned how to attach my file on this community lol, the problem is on the last worksheet right next to Chooser. The filters at the left-hand side only control two charts on the left (they are using the same excel file), and the filters at the middle control the map on the right (different excel file).


            I really appreciate for the help!


            Best regards,


            • 3. Re: Connect two excel files
              Warren Holroyd



              im on my phone atm, but I note you've uploaded a zip.  Have a read up on how to upload a packaged workbook (tbwx).


              if I get chance this afternoon, ill instal T9 and take another look.




              • 4. Re: Connect two excel files
                Warren Holroyd

                Hi Andy,


                Yep, your zip just contains a twb file.  A twb file is nothing more than a glorified xml file... which is to say that it's nothing more than a set of coded instructions that tell Tableau what to do with the data it's connected to.  A twb file doesn't actually contain any data (as the data is still located on your hard drive or server).


                In order for the community to help you, we'd need a twbx file.


                Briefly read: https://www.interworks.com/en-gb/blog/anonymous/2012/01/25/twb-vs-twbx-whats-difference


                Save your workbook as a twbx file and upload that.

                1 of 1 people found this helpful
                • 5. Re: Connect two excel files
                  Andy Hsieh

                  Hi Warren,


                  Thank you for the instruction. Here is my twbx file

                  • 6. Re: Connect two excel files
                    Warren Holroyd

                    Based on the file you've supplied, the solution looks fairly straight-forwards.  You're like 90% of the way there.


                    Pull "Chooser" into your dashboard and make it operate as a filter by clicking:




                    Clicking a name in the "Chooser" table will now act as a filter across all vizzes in the dashboard.


                    You can then get rid of your individual name filters.


                    I can't validate the rest of your method as I'm struggling to trace fields with the Japanese(?) characters.


                    Check the attached file.


                    Couple of other points:


                    • Remember that you're now using a twbx file, so you'll need to re-establish your data connection to your local data (should it change).
                    • You might want to avoid using floating containers in your dashboard as they don't dynamically size very well and make screen/device management difficult (your viz may not look as you expected when it's displayed on a smaller screen)
                    • Your underlying viz style is really nice and matches the subject matter well.  Look forwards to seeing the final result.


                    Best of luck,


                    1 of 1 people found this helpful
                    • 7. Re: Connect two excel files
                      Andy Hsieh

                      Thank you so much Warren!!!


                      No worry about the data connection, I have done the work with my twb file by following your instruction.


                      I don't know how to arrange my charts without using floating, that could be my next problem if the users are using other devices rather than laptop.


                      But right now I am dealing with something more important. As you can see, I have a customized map (baseball field) and some dots on it (drop points), and these dots are made with X,Y values. User first needs to find the X,Y values within Tableau by using annotate point, and then enter the X,Y values into the excel file with the pitcher name, batter name, whether it's a hit or not, etc, so the dots will actually react to the filters.


                      Is there a simple way to do it? I'm thinking that to make a webpage so the user can click the map and choose pitcher name, batter name, hit or not, and hit a button to submit the data into a database or excel file, and Tableau can update the data automatically. Does this sound workable for you?


                      If anyone knows how to do it but can't deal with the foreign characters, I can change those to English.


                      Best regards,


                      • 8. Re: Connect two excel files
                        Warren Holroyd



                        I'm afraid you've lost me slightly.  I'm not sure what the intended outcome of this project is.  The purpose of Tableau is to provide an environment in which the end-user doesn't need to look at the data source.  You would typically present the end-product on Tableau Public, on a Tableau Server, or pass the twbx file over to someone with a free Tableau Reader.


                        You seem to be talking about two separate abilities:


                        1. The ability to filter marks (dots) and update the end presentation/dashboard.  This is something Tableau is capable of doing, without the need for external products or a web interface.  Every field is capable of being filtered within Tableau and those filters can be made available to the end-users of the dashboard.  You seem to have understood this as I note that you've added a second "chooser" table as a cross-datasource filter.  Could you not extend this principle further?
                        2. The ability to update a source database with new data, which can then be read by the Tableau dashboard.  This is a very different issue and probably requires a thread of it's own.  It also raises the question of how you collected the data you already have and what the future method of collection will be.


                        Not sure I can help as I'm not sure what the exact problem is.  Attached is a version where your dashboard objects are tiled, rather than floating.  It's fairly easy to use tiles.  You just snap objects to the edge of other objects.


                        dashboards, containers, tiled

                        Resize and Lay Out Your Dashboard


                        If this or other posts have helped/answered, mark them as such.


                        If you can clarify how the end product will work (and maybe how the end-user will interact and use it), perhaps I can help further?

                        1 of 1 people found this helpful
                        • 9. Re: Connect two excel files
                          Andy Hsieh

                          Hi Warren,


                          Sorry about the confusion. The company that I work for owns a baseball team. My manager asked me to use a software like Tableau to display the data so the coach can decide who will play the game base on the opponent pitcher. Therefore, I have to come up with a chart that contains batting average, on-base percentage, slugging percentage, and total average, just like the two charts on the left-hand side. Also, my manager wants to tell the coach that technology can really help the team, so he asked me to present more than the coach asked for. For instance, whether the ball was a hit or not and where the ball dropped on the field after hit, and the other dashboard for the defend statistic. And all the above should be related and can be controlled by the same filters.


                          However, the baseball team does not have enough data for me to use. In fact, the data that they gave me only contains one game (I came up with another fake game data so I can draw the line chart). Therefore, the software that I use have to be able to update with new data into the database. For now, I have to enter the data into the Excel file to calculate the above four statistics, and I have to enter the data into another Excel file with the X,Y value that I found in Tableau to display where the ball dropped after hit, and the pitcher and batter name within the same row. All these steps are too complicated for the recorder to record the data, and I am afraid that the team will abandon the tool because it takes too long to enter the data. Therefore, I need to come up with a better way for them to enter the data.


                          Please let me know if there are still any confusion!!

                          I know that maybe I should not use Tableau since I need to update my data so often, but I really like how Tableau display their charts and how the system combine the data. Using another software is my last option for now.


                          Thank you again for your help!!


                          Best regards,