2 Replies Latest reply on Jul 31, 2018 12:18 AM by Rico de Feijter

    Merge contents of multiple datasources on a sheet

    Rico de Feijter

      Hi,

       

      The story below concerns the attached workbook in which Query is a Microsoft SQL server datasource and 2018 - 3+ Holding is an Excel datasource.

       

      In sheet 1 I have the following table (note the screen below shows an excerpt from the table. Also note the datasource, which is "Query" in this case). The table consists of ParentRekeningen, Rekeningen and 'Trailer' values:

       

      In sheet 2 I have another table, which is similar to the one above. The difference is that another datasource is used with other values (see 'Holding' values). The 'ParentRekeningen' and 'Rekeningen' are partly the same and some are unique (i.e. ParentRekening FFA? Fixed Financial Assets and its Rekeningen (0191, 0192 etc.). These do not appear in sheet 1, datasource Query):

       

       

      In sheet 3 I want to consolidate the contents from sheet 1 and sheet 2. However, up to now I only managed to merge the ParentRekeningen and Rekeningen that both datasources have in common (below an excerpt from the table in sheet 3 is shown. An example of a common ParentRekening/Rekening is 0210. Trailers has a value of -18.000, while holding has a value of -18.160):

       

      However, is there also a way to add the ParentRekeningen and/or Rekeningen that are unique to the datasource?

       

      So for instance, Rekening 0191 does not appear in the Query datasource, but does appear in the 2018 - 3+ holding datasource. How can we still add Rekening 0191 (and other ParentRekeningen/Rekeningen of the same sort) to sheet 3 such that Trailers gets value 0 and holding the value of the corresponding Rekening (e.g. when 0191 is added to the view, we get ParentRekening->FFA? Fixed Financial Assets --- Rekening->  0191 --- Trailers->0 --- holding->2)?

       

      Note that also a parameter is present in the sheets. This parameter should still work and is of importance to select the correct period.

       

      Thanks in advance for the support.

        • 1. Re: Merge multiple datasources on a sheet
          kumar bharat

          Hi Rico,

          Instead of blending why dont use direct joins or cross joins.Joins are better than data blends and give accurate results.

          BR,

          bharat

          • 2. Re: Merge contents of multiple datasources on a sheet
            Rico de Feijter

            Hi Kumar,

             

            I cannot use joins as some of the rekeningen are unique to the data source (as explained in my question) and must also be taken into account. Then you can do a left join of course but that still will not give me the correct results. It will 'Join' all the columns of both datasources, which will not give me the desired result.

             

            Also the underlying Query datasource is a Microsoft SQL server datasource and the 2018 - 3 holding datasource is Excel, which causes a problem if you want to Union data. Union can only be done when the datasource is of the same connection: Union Your Data:

             

            "You can union your data to combine two or more tables by appending values (rows) from one table to another. To union your data in Tableau data source, the tables must come from the same connection."

             

            I also looked into appending data. The problem then, however, is not having the possibility to dynamically refresh the data.