1 2 Previous Next 16 Replies Latest reply on Feb 6, 2018 1:54 AM by Norbert Maijoor

    Combine two excelfiles show all data

    daniel.andell

      I have an SQL server with sales back to 2016. In this file I dont have the quota. Therefore I would like to add another datasource

      with the quotas in.

       

      Now my problem begins. Some of the customers have no quota and some has no sales.

       

      I would like to start from sales because this SQL server includes lot more information. But if the customernumber has no sales 2018 their quota wount show.

       

      Is there a simple way o create a calculation or something that helps me add information that dissaperas. I cant make changes to the SQL but to the budget if that is helpful information.

       

      Attached an example

       

      Hope you understand my question.

        • 1. Re: Combine two excelfiles show all data
          Norbert Maijoor

          Hi Daniel,

           

          An option to be evaluated could be "scaffolding" and is explained here.

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: Combine two excelfiles show all data
            Zhouyi Zhang

            Hi, Daniel

             

            One possible solution could be union your two data sources as shown below. I haven't try one from SQL Server and union to excel file, but it should work.

             

             

            ZZ

            1 of 1 people found this helpful
            • 3. Re: Combine two excelfiles show all data
              daniel.andell

              Zhouyi Zhang thanks for that suggest. I is a great feature I havent seen before. As far as I understand this only works for data in the same file correct?

               

              In my real file I have one SQL database and then I would like to add quota from an excel file. This doesnt work then

              • 4. Re: Combine two excelfiles show all data
                daniel.andell

                Thanks Norbert Maijoor I am not sure how to make this work in my case.

                 

                I use the SQL sales as my primary and then it matches every customer that has a quota. But it doesnt match customers who as a quota but no sales.

                 

                My understanding is that I can use scaffolding to make them work togather. But I am not sure how to do that. I know that all my customers who has a quota also has sales at some time. So the information about the customers etc are all in the SQL. Is there a way to tell Tableau to add the customers that only has a quota to the list of active customers so that their quota shows?

                 

                Would appricate if you could help me in the example I sent.

                 

                Thanks a lot

                • 5. Re: Combine two excelfiles show all data
                  daniel.andell

                  Another idea I have is to create another column in the budget spreadsheet called Zero sales.

                  If I can combine this Zero sales with the sales from the SQL file could this make all customers with a budget appear?

                   

                   

                  • 6. Re: Combine two excelfiles show all data
                    Norbert Maijoor

                    Hi Daniel,

                     

                    Find my approach based on "Scaffolding" stored in attached workbook version 10.5 located in the original thread.

                     

                    Upfront not sure if it could be applicable in your scenario but you will get a clear view how it is done.

                     

                     

                     

                    2. Scaffold dataset with all entries

                     

                     

                    3. Relationship

                     

                     

                     

                     

                     

                    2. M1. Sales (Scaffold): ifnull(SUM([Sales].[Sales]),0)

                     

                    3. M2. Budget (Scaffold): ifnull(SUM([Budget (Sales)].[Budget]),0)

                     

                    4. Drag the required objects to the indicated locations

                     

                     

                    Regards,

                    Norbert

                    • 7. Re: Combine two excelfiles show all data
                      daniel.andell

                      Thanks Norbert Maijoor

                      I have been trying to follow your steps and as far as I understand the scaffold inclydes the data from both files?

                       

                      Then i guess this way of doing it wount work bcause the SQL I have will be updated automatically and I cant do anything with it.

                       

                      If I need to scaffold the files the data risk being incorrect if I havent updated the scaffold when changes are made?

                       

                      Am I thinking right here or have I missunderstood?

                      • 8. Re: Combine two excelfiles show all data
                        Norbert Maijoor

                        Hi Daniel,

                         

                        Below my "feedback" concerning your questions.

                         

                        1. I have been trying to follow your steps and as far as I understand the scaffold includes the data from both files?

                         

                        Yes & No. The scaffold includes the data from both files & the "unknown". If it was "just" the data from both files a join was sufficient.

                         

                        2.Then i guess this way of doing it wont work because the SQL I have will be updated automatically and I cant do anything with it.

                         

                        No the scaffold won't be updated on the "unknown" part. I could imagine you could "automate" your scaffold by "UNIONING" the datasource but will only bring in the "known"-values.

                         

                        3.If I need to scaffold the files the data risk being incorrect if I havent updated the scaffold when changes are made?

                         

                        This is a bit a "The Chicken & Egg-story". Your datasources aren't giving you the "'complete" dataset so you need a scaffold but yes indeed the scaffold remains only a "template" which is build from your "manual" source.

                         

                        Hope it's clear

                         

                        Regards,

                        Norbert

                         

                         

                        • 9. Re: Combine two excelfiles show all data
                          daniel.andell

                          Thanks again Norbert Maijoor

                          Since I know I have all the customer in the SQL database is there a way to just create an action for the sales to show all customers in the database no matter if they have sales during 2018 or not?

                           

                          If they are just showing I can create another calculation telling that if sales is 0 and budget is 0 then remove and I am back with the sales database but it wount miss budget if sales are empty?

                           

                          Is it possible to make it this way instead, if so how would I create a calculation like that?

                          • 10. Re: Combine two excelfiles show all data
                            Norbert Maijoor

                            Hi Daniel,

                             

                            "From the TOP of my head". If all customer are in the SQL database, based on Budget or Sales datasource a UNION could automate the complete Customer-population.

                            Be aware that you can only view data on Customer level and not at Month, Year, Product & Salesrep level

                             

                            Regards,

                            Norbert

                            • 11. Re: Combine two excelfiles show all data
                              daniel.andell

                              Norbert Maijoor but can I use union when I have two different datasources that only works with on and different sheets i think?

                              • 12. Re: Combine two excelfiles show all data
                                Norbert Maijoor

                                Daniel,

                                 

                                ARCHHH! Looked over that one;( thinking two tables in one database. your budget & Sales are two different datasources in your SQL database and you can't UNION them

                                 

                                Regards

                                Norbert

                                • 13. Re: Combine two excelfiles show all data
                                  daniel.andell

                                  The SQL database is for all our markets and it is standardised but I need additional information like local quota etc. That’s why I plan to load this seperated. Then I noticed that some customers has quota but no sales yet. I would like to make sure the data is correct no matter what customers or product groups has sales so far.

                                  • 14. Re: Combine two excelfiles show all data
                                    Norbert Maijoor

                                    HI Daniel,

                                     

                                    NEXT;).....Upfront I don't have a lot experience with this techniek but worth while exploring and stored in attached workbook version 10.3 located in the original thread.

                                     

                                    I defined two datasources Sales and Budget based on excel-file. Not sure if you can do this with two different databases on SQL.....

                                    Defined a "Join calculation" for each datasource and define a "Full Outer" Join

                                     

                                     

                                    It will give me all Customer values from Budget & Sales, Column Customer (Budget) & Customer

                                     

                                     

                                    I can "merg" both column based on follow syntax

                                     

                                    1. D1. Merger:

                                     

                                    if isnull([customer (Budget)])=True then [customer]

                                    elseif isnull([customer (Budget)])=False then  [customer (Budget)]

                                    elseif isnull([customer])=True then  [customer (Budget)]

                                    elseif isnull([customer])=False then  [customer (Budget)]

                                    end

                                     

                                     

                                    let me know your thoughts on this one

                                     

                                    Regards,

                                    Norbert

                                    1 of 1 people found this helpful
                                    1 2 Previous Next