1 2 Previous Next 16 Replies Latest reply on Jun 7, 2018 1:00 AM by Mavis Liu

    Can I join 2 access tables that have no common field?

    naresh.suglani.0

      I have 2 tables I want to link but they have no common fields to join on.

       

      Is this possible at all?

        • 1. Re: Can I join 2 access tables that have no common field?
          Mavis Liu

          Hi Naresh,

           

          You can do a blend in Tableau, which means you can build worksheets based on different data sources and bring them all in the same view in a dashboard.

           

          You could also use both data sources in the same worksheet, however you need a field to link/blend on.

           

          What's the use case for these two data sources in your situation? It'll help us understand better.

           

          Thanks,

           

          Mavis

          1 of 1 people found this helpful
          • 2. Re: Can I join 2 access tables that have no common field?
            naresh.suglani.0

            Hi Mavis, one table contains sales volumes and the other contains revenues from those sales so we needed to calculate the average revenue per sales by product type as well but these 2 tables contain no ID field that can be linked on, only week number and year.

            • 3. Re: Can I join 2 access tables that have no common field?
              Mavis Liu

              Hi Naresh,

               

              Do you have product type in both these tables?

               

              You can create a calculated field before a join/blend, so is there a field which has something that can be used/converted into Product Type?

               

              Say for example, we have dataset one with :

              product key

              A

              B

              C

              D

               

              Then dataset two with:

              Product:

              Gel

              Watch

              Glasses

              Shorts

               

              If we know Product Key A = Glasses, then we can put this in as an alias or a calculated field in Tableau.

               

              Thanks,

               

              Mavis

              • 4. Re: Can I join 2 access tables that have no common field?
                naresh.suglani.0

                Hi Mavis I am attaching a sample of the data here showing the 2 tables I want to connect on. One is revenue and the other is volume. Both contain a product name (subcode) but we need to calculate revenue/volume and be able to split by week, year, product type, or CC_Group3 where necessary.

                 

                We tried a LOD calculation using the include and fixed terms but the values being shown were too low. For example, using the entire database of records we had a revenue of 2 million for a certain region and product with sales of 490. This should have give an average of 4,000 but instead it showed only 1,297.

                 

                I'm unable to share the workbook but hoping the sample database I've attached will help you understand more and provide a solution regarding the calculations I need to create.

                 

                Thanks in advance, and hope you understand.

                • 5. Re: Can I join 2 access tables that have no common field?
                  Mavis Liu

                  Hi Naresh,

                   

                  Is this what you're looking for? :

                   

                  2018-06-06_13h30_49.png

                   

                  Because you don't have a unique identifier in your datasets, then it's doing a many to many match. To fix this issue, I have done a blend on the dataset. To do this connect to your first table (volume) and go back to the worksheet and go to Data- > New Data Source

                   

                  2018-06-06_13h34_03.png

                   

                  Then select the second table (revenue).

                   

                  You should then see two data sources in the top left:

                   

                  2018-06-06_13h35_01.png

                   

                  Start building a view (e.g. bring year no to columns) and you should see a blue tick against one of your data sources, this is now your PRIMARY data sources:

                   

                  2018-06-06_13h36_16.png

                   

                  Now click on the other data source and you should see some chains:

                   

                  2018-06-06_13h36_51.png

                   

                  To activate the link, click on the chain until it is red. In your case, you should select Yearno, Week No and also Sub Code.

                   

                  if the chain isn't available, go to Data-> Edit relationships.

                   

                  I have attached the sample workbook for you.

                   

                  More information on blends here: Blend Your Data

                   

                  Thanks,

                   

                  Mavis

                  • 6. Re: Can I join 2 access tables that have no common field?
                    naresh.suglani.0

                    Hi Mavis thanks for putting this together for me it was very well laid out and easy to follow, and something similar I have done before but...........

                     

                    One question - how do I then calculate average sales by product type and region? We need a separate column showing the revenue by product and/or region per sales (volume).

                     

                    Thanks in advance,

                    • 7. Re: Can I join 2 access tables that have no common field?
                      Mavis Liu

                      Hi Naresh,

                       

                      You could activate the blend based on CC Report Region and then bring it into the view and change the measure aggregation to AVG:

                       

                      2018-06-06_14h10_34.png

                       

                      2018-06-06_14h11_04.png

                       

                      Or was there another way you wanted to visualise this? If there is, please kindly provide a mockup.

                       

                      Thanks,

                       

                      Mavis

                      • 8. Re: Can I join 2 access tables that have no common field?
                        naresh.suglani.0

                        Hi Mavis many thanks for your reply. Please see the attached workbook that I have created using your steps above.  Let me know if you can open it ok.

                         

                        To explain what I need if you look at the numbers for Sub Code (standard) and North West Area you will see revenue of 82,234 and volume of 19.

                         

                        At this point I'm not sure how to add what these numbers refer to. In the database tables they come under the field "data type". In one table they are called FUNBBF1 and in the other they are called FUNVOL.

                         

                        That is one issue I am hoping I can solve so they are easy to read and see what the numbers refer to.

                         

                        Now, the main issue. I need a separate row showing the average revenue so for the above numbers (82,234/19) the value should be 4,328.

                         

                        We are then able to show the average revenue made from each product type for any time period.

                         

                        I hope this now makes more sense.

                         

                        Hope you can find a solution for me.

                        • 9. Re: Can I join 2 access tables that have no common field?
                          Mavis Liu

                          Hi Naresh,

                           

                          You could bring data type from source one into columns, and then data type from source two into your rows:

                           

                          2018-06-06_15h04_05.png

                           

                          Then to find the average, make the calculation:

                          Average

                           

                          SUM([Revenue (Dummy Data)].[Sum Of T Value])/SUM([Sum Of T Value])

                           

                          Then bring this into your view.

                           

                          What I would advise doing is to actually label the labels so that you know whether it's revenue, average etc.

                           

                          To do, when building your view, add in your first measure to the table and then drag a second measure over the crosstab so that you get a 'Show Me':

                           

                          2018-06-06_15h06_01.png

                           

                          That means tableau will create a bucket for measure values and have measure names to split the rows up:

                           

                           

                          2018-06-06_15h07_14.png

                           

                          It might be worth renaming your Sum of T Value fields to be 'Revenue' and 'Volume' to make it clearer like this:

                           

                           

                          2018-06-06_15h08_01.png

                           

                          So then we get this:

                           

                          2018-06-06_15h08_29.png

                           

                          Thanks, I've also attached a workbook if you'd like to take a look.

                           

                          Mavis

                          • 10. Re: Can I join 2 access tables that have no common field?
                            naresh.suglani.0

                            Thank you so much for this Mavis, it worked perfectly and I have now replicated it to my live database as shown below. I just need to clean up the formats to it looks better to present.

                             

                            Output 1.jpg

                            • 11. Re: Can I join 2 access tables that have no common field?
                              naresh.suglani.0

                              Hi Mavis, although this works fine now I have one problem which I didn't expect but needs to be solved if possible.

                               

                               

                              If you change the name of the regions to remove the region number code (using edit alias) 2 values are lost from the table leaving only the value for volume behind. Why is this?

                               

                              The example below shows I have changed the last column to "Scotland". You can see how it affected the table in the workbook you sent me. The same thing happened when I did this to my live workbook.

                               

                              Region name change problem.jpg

                               

                              Hope you can find a solution.

                              • 12. Re: Can I join 2 access tables that have no common field?
                                Mavis Liu

                                Hi Naresh,

                                 

                                This is because your blend is based on the region. If you change the alias then the 'link' you had activated for the blend would no longer work. This is because it uses the new alias name as a matching criteria.

                                 

                                What I would suggest doing instead is making the change on both data sources to keep it consistent. Or creating a grouping field which you can use as your new name.

                                 

                                In my below example I have updated the sub group name from standard to standardee and used it in the viz:

                                 

                                2018-06-06_16h22_46.png

                                 

                                2018-06-06_16h22_55.png

                                 

                                2018-06-06_16h23_41.png

                                 

                                Thanks,

                                 

                                Mavis

                                • 13. Re: Can I join 2 access tables that have no common field?
                                  naresh.suglani.0

                                  Hi Mavis I'm not quite sure I understand what you meant by grouping the subcode. I would like to rename all of the regions from the original format, for example, "08851 SCOTLAND AREA" to "Scotland", and not lose any information from the resulting table.

                                   

                                  Are you able to show me via the workbook you sent earlier please? I appreciate your time in helping me today.

                                  • 14. Re: Can I join 2 access tables that have no common field?
                                    Mavis Liu

                                    Hi Naresh,

                                     

                                    Subcode was just an example that I used. Using your field cost centre information, I right clicked on this field and selected create group:

                                     

                                    2018-06-06_16h55_43.png

                                     

                                    Then I selected Scotland and chose group:

                                    2018-06-06_16h56_00.png

                                     

                                    Then I clicked ok and put it on the columns shelf to replace the previous cost centre field:

                                     

                                    2018-06-06_16h56_51.png

                                     

                                    The group field should have a paper clip next to the field name in the data pane. If you look at the right of the table, now you can see the group name SCOTLAND has been used. Attached the workbook with more information.

                                     

                                    Thanks,

                                     

                                    Mavis

                                    1 2 Previous Next