12 Replies Latest reply on Oct 30, 2019 10:01 AM by Jonathan Drummey

    union not being successful

    Benoit Carer

      Hi All,

       

      I am an new user of tableau and have some issue with union join or blending ( I have being trying all of them without getting the end result I was looking for)

       

      Hereafter is my scenario which does seems complicated but I don't seem to be able to solve it :-( :

      I have two data sources : One being actual on account level and other one budget on account level, but I have accounts on both source which do not have a corresponding party on the other side , therefore when I am trying to build a view that show me total Budget + total actual on account level it does not show me the data that do not have  a corresponding dimension on the other sources

       

        

       

      Could you please help me out getting this basic thing working ,

      Thanks in advance for your help

      Ben

        • 1. Re: union not being successful
          Budi Lubis

          Hi Benoit -

          You can use full outter join to combine or full union.  Sample for Union attached.

           

          Full Outter Join

          Screenshot.png

           

          Union

          Screenshot.png

          • 2. Re: union not being successful
            Benoit Carer

            Thanks Budi for your quick answer ,

            I tried that but then I am still facing an issue as when I am picking up the  Account dimension from Actual source unmatched account from the budget will not be included, same if I use the Account dimension from the budget data source I will have all account from the Budget but will be still missing the account included only in actual source ? or do I do something wrong ?

             

            Thanks,

            Ben

            • 3. Re: union not being successful
              Michael Hesser

              Hi Benoit;

               

              Try creating a FULL OUTER JOIN between the files, with your joining field as account.  Here's a sample I threw together:

               

              You'll still need a master Account number, though, so this might work:

               

              Account Number

              iif(isnull([Account]),[Account (Budget)],[Account])

               

              You can format your NULL values, replacing them with zeroes, and you should be good!

               

              Will this help? --Michael

              • 4. Re: union not being successful
                Budi Lubis

                Hi Benoit -

                Try the full union.  See the sample attached.

                 

                Screenshot.png

                1 of 1 people found this helpful
                • 5. Re: union not being successful
                  Jonathan Drummey

                  FYI Tableau has a convenience function: IFNULL([Account], [Account (Budget)] will return the same results as as the IIF() statement.

                  1 of 1 people found this helpful
                  • 6. Re: union not being successful
                    Benoit Carer

                    Thank you all , this is definitively helpful !

                    I was basically missing the Master account number part on my overview , I did it using convenience function : IFNULL([Account], [Account (Budget)] but then when I tried to build up other visualization using the master account number as a filter is not possible , it showing me the * and cannot convert this measure into a dimension so I not sure what I need to do next to be able to include the full list of account ( including grouping if possible) .

                     

                    Thanks again for your support

                     

                    Regards,

                    Ben

                    • 7. Re: union not being successful
                      Jonathan Drummey

                      Hi,

                       

                      Can you share a packaged workbook or at least screenshots of what you are seeing? If you're getting and * then either you're using ATTR([field]) somewhere or are using a Tableau data blend (which is another way to link data which is not required here) or both.

                       

                      Jonathan

                      • 8. Re: union not being successful
                        Benoit Carer

                        Hi All ,

                        I finally got it the way I wanted it ,  So I actually use the full outer and create a master Account via calculated field, on high level and the visualization was just fine, then I wanted to use a dimension "project" ( included in both sources) to see the details project by project or using this dimension as filter to see individual result , these project dimension were connected via the full outer join as well, similar to my account and this was not working, for some reason by selecting the project the account reflected were only the one of one of the data source.

                        Therefore as  work around I added a calculated dimension using the Fixed LOD on the project as well and then everything is working just fine.

                         

                        Then next challenge : I am connecting 5 data sources  with the main connection dimension being "project" the Actual (Source 1) and Budget (source 2) sources are connected by Full outer on project level, the 3 others sources are connected via blending on project level as well with the other sources , but as I am trying to use dimension from source 3, 4 & 5 on views, I am getting the following error message :"An error occurred while communicating with the data source : "Cannot blend the secondary data source because one or more fields use an unsupported aggregation." and cannot do anything with my other sources "

                        would anyone know, how to handle this issue for me to be able to use dimension from the 5 sources together ?

                         

                         

                        Thanks all for the help , highly appreciated

                        • 9. Re: union not being successful
                          Michael Hesser

                          Hi Benoit: are you using "non-additive" calculations such as COUNTD and MEDIAN?

                          Jonathan Drummey wrote about this in a much earlier version of Tableau, but it may still hold true today:

                          Cannot blend the secondary data source because one or more fields use an unsupported aggregation

                           

                          Are your data sources live or extracts?

                          • 10. Re: union not being successful
                            Benoit Carer

                            Hi Michael,

                            thanks for your quick answer,

                            No , I created lot of calculated measures but none of the Non-additive ones.

                            Thanks for the article I will have a look

                             

                            Sources 1,2 were live and source 3,4&5  were extracts

                             

                            Regards

                            • 11. Re: union not being successful
                              Michael Hesser

                              I've had similar problems when I try to use Live sources (especially for calculations requiring an Index or Countd).

                              To determine if this is a limitation of your data type, can you see if the error persists if you convert your live sources to extracts?

                               

                              If that doesn't work, you might consider opening this up a new question in the forums: this will re-open the question to a larger audience who might miss it otherwise.

                               

                              Talk soon!

                              • 12. Re: union not being successful
                                Jonathan Drummey

                                FYI what I wrote in that earlier post is still true, with two changes: a) Since DB2 (data blending 2) was introduced there are some cases where non-additive aggregates will work that didn't work in earlier versions due to Tableau's data engine gathering more features. b) In some cases level of detail expressions can also cause the non-supported aggregation error.

                                 

                                Also INDEX() shouldn't be causing any problems for data blending because it's a table calculation that is computed in Tableau, the issue is more likely to be a situation where there are other aggregations in the view.

                                 

                                Data blending 2 (that triggers the non-supported aggregation error) has three triggers: using a dimension from a secondary source in the view; using a dimension from a secondary source on Filters; and using a linking dimension where the linking field from the primary source isn't in the view. If you can set up your view so none of these conditions are met then Tableau will use data blending 1 (the initial version of data blending) and most likely you won't get that error (without seeing your view I can't be completely sure). However doing that can add extra complexity to views that can make it difficult to impossible to get the rest of the aggregations & interactivity & display to work as desired...this is one reason why I recommend using joins & pre-processing of the data for production views rather than data blending.

                                 

                                Jonathan

                                1 of 1 people found this helpful