3 Replies Latest reply on Jun 22, 2017 9:25 PM by Wilson Po

    Appending Join Tables in Tableau on a monthly basis

    Junior Ramroop

      Hi Team,

       

      I have two files per the image below, where I am trying to do a Join using a unique ID to combine as 1 table. The first data source is updated on a monthly basis while the second data source is updated on a quarterly basis. I would like to be able to append the first file on a monthly basis, while keeping the data for prior months, in addition to updating the Enhancement file on a quarterly basis as well.

       

       

      One option is to do a data blend however, I am unable to do LOD functions so I am defaulting to Join. Is there anyway I can achieve the above using a join?

       

      Thank you,

      Junior A Ramroop

        • 1. Re: Appending Join Tables in Tableau on a monthly basis
          Wilson Po

          Hi Junior,

          You can certainly create the unions for appending new files for the Monthly Billing and Quarterly Data each month/quarter, but this will need to be a manual process of updating the data connection.  Ideally, new records are simply added to the data files directly outside of Tableau, so that information is updated to complete this join without changes to this schema. 

           

          For automating this with Tableau Server/Online refresh, this might be slightly outside of what extract refreshes are designed to do.  While we can schedule append to extract files, it is done to the entire data as represented by all tables rather than specific tables at different intervals.  If that is a requirement, you might take a look at ETL tools like Alteryx that can manage these data transformation workflows and automate the output for Tableau.

          1 of 1 people found this helpful
          • 2. Re: Appending Join Tables in Tableau on a monthly basis
            Junior Ramroop

            Hi Wilson,

             

            Thank you for your response below. Perhaps I am not understanding your suggestion correctly but I’ll try explaining another way given what you mentioned above. Let’s say I am able to join the two files outlined in my original question where the monthly file has 20 columns for example and the second 6 columns. My join table now has a total of 26 columns. Next month I have a new monthly file (20 columns of data), how do I manually append to the 26 column join without losing or replacing data that is already there? Appreciate your assistance on this.

             

            Thank you,

            Junior A Ramroop

            • 3. Re: Appending Join Tables in Tableau on a monthly basis
              Wilson Po

              Hi Junior,

              There are a few ways to handle the append:  either directly in the data or through Tableau's Union functionality.

               

              The former solution simply is opening the data source an adding the new records to the same table below;  This expands the monthly billing file to really collect multiple months of data.  From a Tableau perspective, the record size has increased but there is little change to the actual schema used for connection (which means Tableau will continue to handle the data in the same way but with multiple monthy billing months of data)

               

              Alternatively, Tableau's Union capability is essentially an append to new records.  As long as the records are stored in the same DB or file as the prior months, it will append the tables together to create a longer table involving multiple months.  This still works when joined to a separate table:  

              Unions and Joins.jpg

              As you can see, there are multiple stacked tables under monthly data - representing a union of multiple months worth to that portion of the data set.  This is then joined to a more consistent 6 column dimension table as you described.  I would play around with Unions to see if it will work out for your use case;  obviously if that does not, then you can default back to the solution of adding records directly to the table as well.