7 Replies Latest reply on May 15, 2015 12:09 PM by Ajinkya Bhonsle

    Data gets changed when I join tow worksheets

    Ajinkya Bhonsle

      I have two excel worksheets on tableau. When I join the two worksheets with a common column. The data gets changed.I am trying to compare Sales Forcast Vs Billings

        • 1. Re: Data gets changed when I join tow worksheets
          Douglas Everson

          It would probably be easiest if you can show an example (an Excel document or ideally the Tableau document where you are having the problem). If that isn't possible...

           

          Where are you joining the datasets? Are they in the same Excel and thus can join before you load the data or are you trying to use data blending? Is there a 1 to 1 relationship with the two data sets? If not, is there a 1 to many relationship? (i.e. if you know SQL, you can get all your data using a "Left" or "Right" join without adding any additional rows)

          • 2. Re: Data gets changed when I join tow worksheets
            Ajinkya Bhonsle

            Its one excel with 2 sheets in it. One sheet is "Forecast" and other is "Billings". When I upload my excel. I can see two sheets in tableau "Forecast" and "Billings". When I pull the Forecast sheet into the pane. I see certain data and when i pull Billings sheet. The data in the forecast sheet gets changed. Tableau by default joins both the sheets on the basis of common filed "Part#" and "Customer Name".Attached are the fields in Forecast and Billings and also with there join condition. I have attached 3 images. What i am looking for is even after joining the "Billings" Sheet the data should not get changed in Forecast. I also tried Left join, inner join. It does not work. Also if i try to join only on "Customer Name", the data changes again.

             

            Info: The common fields between the tow sheets are "Customer Name" and "Part#". The

             

            Also I wanted to know why isn't the Left Join and Outer join cannot be selected.

            • 3. Re: Data gets changed when I join tow worksheets
              Douglas Everson

              From what I can tell, it looks like the rows are getting duplicated when you do the join. How many rows in billings is there when Customer name = ?MOD (P) (can't tell what the first letter is) and Part = MPU-9250? Is it more than one? (My guess is that there are 4?) If there is more than one, the row in forecast with that name/part is going to get duplicated however many times so that the new columns from billings can be created. If there is only one, then I don't know what is going on.

               

              You can see what dataset is created by right clicking the dataset on the top/left pane on your worksheet and clicking "View Data" to troubleshoot specific incidences where this is happening.

               

              If the above is the issue, you might be able to solve your problem by using "AVG" instead of other aggregations. But the best solution would be to figure out a better join your data set (if available).

               

              I'm not sure why Right Join is not available. I believe Outer Join is not available because it is not supported in Excel. (You can do a right join by switching the 'primary' and 'secondary' tables around.)

              • 4. Re: Data gets changed when I join tow worksheets
                vishwanath Pendyala

                Hi

                 

                Make sure that you have right join conditions as it clear states your data between the two sheets has not been joined correctly.

                 

                Thanks

                Vishwa

                • 5. Re: Data gets changed when I join tow worksheets
                  Ajinkya Bhonsle

                  Douglas there is one to many relationship between the data sheets.And the values get multiplied the number of times it shows in any of the sheets.For eg: if there is one record of Part#MPU-9250 in Forecast  there will be multiple records in Billings report. This condition is viceversa. And I did use the aggregate function. It does give correct values for certain records but not for all.


                  Vishwanath - II have joined both the tables only on Customer Name and Part#. There are no common fields. DO I need to create a unique Id on both the sheets? But our whole purpose is to eradicate the excel work and start using Tableau for comparing the sales reports

                  • 6. Re: Data gets changed when I join tow worksheets
                    Douglas Everson

                    Yes, you will need to create a unique ID on both sheets unless there is an additional item you can join the sheets on that will uniquely identify them. Otherwise, you'll continue to get the duplication of rows.

                     

                    As stated above, depending on what you are trying to do with the data, you may get it to work by choosing "Average" as the aggregation instead of "Sum." Do this by right clicking on a measure, highlighting "Measure (Sum)," and then selecting average. If you need to do any sort of real analysis, you'll likely need to fix your join conditions though.

                    • 7. Re: Data gets changed when I join tow worksheets
                      Ajinkya Bhonsle

                      Thank you Douglas. I did resolve the issue by having a unique ID and fixing the join condition.