12 Replies Latest reply on Dec 17, 2019 7:48 AM by Kaz Shakir

    Struggling with 2 data source

    Graham Chau

      Hi there,

       

      I am struggling to get the right number of bookings

      I have:

      1st data with the target, consultant etc

      2nd data with bookings, value.

      The problem is that each booking has multiple booking reference (in the databse) for same booking:

      i.e.

      reference 42522

      Tour value 150,000

      visa value 250,000

      In the database there are 2 lines, 2 same booking reference for each booking. Sometimes even more (1 bookings = 3 items (visa, tour name etc, 3 same booking reference)

       

      I have joined by departure date, consulant and the value is fine (adding up correctly) but the booking number I either get an error message (showing in red) or just not the right number number of booking, just 1 for everything.

       

      I know it is super basic but it is driving me mad !!

       

       

      thank you

      Graham

        • 1. Re: Struggling with 2 data source
          Ken Flerlage

          Getting this error when I open your workbook. Error opening archive file. Invalid twb or twbx file.

           

          Can you try saving it as a twbx again and reattach?

          • 2. Re: Struggling with 2 data source
            Graham Chau

            I have reloaded it

            is it working now?

            • 3. Re: Struggling with 2 data source
              Kaz Shakir

              Graham Chau,

              You might want to consider only joining your tables on the Consultant.  I don't really understand why you would have a Departure date in the Target table - that table just appears to have the personal information for the consultant.

              • 4. Re: Struggling with 2 data source
                Graham Chau

                Hi Kaz

                the data is only few lines of the actual data. I need to join but departure as target is based on a particular departure year and in the data there are many departure I am not interested to show.

                I have also to join (not shown in this example) by booking date but same issue.

                Is it something not possible?

                 

                thanks

                Graham

                • 5. Re: Struggling with 2 data source
                  Kaz Shakir

                  Graham Chau,

                  I don't fully understand, but based on my current understanding, I would say you should still try joining just on the Consultant; and then, in your viz, you can place the departure date on the filter shelf, and filter to just the departure dates you want to include in that viz.

                   

                  Kaz.

                  • 6. Re: Struggling with 2 data source
                    Graham Chau

                    Hi Kaz

                    thanks again for answering

                     

                    it is not that straight forward.

                    The data I have attached it is only an example (not the original)

                    I have dozen of targets to monitor based on:

                    • Departure date
                    • booking date
                    • Consultant
                    • Team

                    The above is coming from excel completed by each team, the sales from the main databased saved in the company server.

                    I want to be able to select for example December target and automatically system shows me based on the criteria of November target i.e.

                    Departure 2020

                    Booking made November only

                     

                    than if I select December system must show me sales based on that target (from the excel sheet) , December will have to show for example

                    • Departure 2021
                    • Booking date December

                     

                    and so on

                     

                    Graham

                    • 7. Re: Struggling with 2 data source
                      Kaz Shakir

                      Graham Chau,

                      So, here's the data you provided in your example:

                       

                      Is the column labeled "Month" in the Target table supposed to be the Booking Date?

                      All of the sales, in the Sales Table, have a departure date of July 1, 2020; and all of the Departure Dates in the Target table are November 1, 2020 - how are those related?  It seems there aren't any matching records between the two tables.  If I join the two tables together on the consultant name and the year of departure I get the following:

                       

                      And with this joined table, if I waned to determine how many bookings each consultant had for every combination of booking year and departure year, I could do something like this:

                       

                      Is that what you are trying to achieve?  If not, could you describe what your output should look like if you are using the sample data you provided?

                      Kaz.

                      • 8. Re: Struggling with 2 data source
                        Graham Chau

                        Hi Kaz

                        how did you achieve the first picture?

                         

                        • 9. Re: Struggling with 2 data source
                          Kaz Shakir

                          Graham Chau,

                          When you look in the .twbx file that I attached to my previous post, you will notice that I am not "blending" the two data sources, but, rather, I am actually joining them.  (Blend Your Data - Tableau )  You can see this from the icons in the Data section.  For example, if you build a viz that uses blended data sources, you will see the following:

                          The fact that one of your data sources has an orange check mark, lets you know that is considered a secondary data source, and therefore you are blending those data sources.  On the other hand if you build a viz from a data source that was joined, then in the data section, it simply looks like a single data source - with just the blue check mark:

                          however, in the "Dimensions" section you will see that there are actually two different tables there - in this case, sales and Target.

                          On the Data Source tab, a single data source looks like this:

                           

                          But, if I join the two tables into a single data source, then the Data Source section will look like this:

                          These two approaches behave differently in Tableau.  If you look at the combines results of these in a viz, you see the following - first is the Joined result, and below that is the Blended result:

                           

                          The biggest difference you will notice between the two tables are the "*" that appear in the Booking Reference and Item Type columns.  The reason for this is that there are multiple values for each of those columns that match the consultant and year of departure.  And with blended data sources, you can not bring over the individual values from the secondary data source, unless it's a one-to-one relationship.  If it's one-to-many, like these are, then you can only bring over some aggregated information - like the total count of the Booking Refernce numbers and Item Types (notice that distinct count does not work):

                           

                          Bottom line is that you should always try to join your tables together, and only use blending as a last resort.

                           

                          Does that answer your question?

                          Kaz.

                          • 10. Re: Struggling with 2 data source
                            Graham Chau

                            Hi Kaz

                             

                            your post will help me tremendously!!

                             

                            It is a new approach I never ever consider while building reports and it will help me to get rid of all the issues I had in the past (like "*", incorrect total etc).

                             

                            I have the final question before I go pls:

                            Can you join 2 datas sources if one is excel and one is SQL or Data stored in Tableau server?

                             

                            thanks again you have been very very helpfull

                             

                            Graham

                            • 11. Re: Struggling with 2 data source
                              Graham Chau

                              I have figured out a way to my question :-)

                              Thanks again Kaz

                              you have been very helpfull !!

                               

                              Graham

                              • 12. Re: Struggling with 2 data source
                                Kaz Shakir

                                Graham Chau,

                                I'm glad you were able to figure out the the answers to your questions.  Yes, you can join different types of data sources (but it does depend on which version of Tableau you are working with).  Also, you should look into what you can do in Excel using the Power Query or Get And Transform functionality there - sometimes it makes more sense to combine your data sources before you get to Tableau - but that's really situation dependent, but it's worth getting familiar with those capabilities in Excel as well.

                                 

                                Kaz.

                                1 of 1 people found this helpful