1 2 Previous Next 22 Replies Latest reply on Aug 9, 2016 7:20 AM by Jonathan Drummey Go to original post
      • 15. Re: How can i use a unique-concatenate aggregation to a string field?
        Jonathan Drummey

        Hi @Manu,

         

        Please don't ask the same question in multiple places as that creates duplication of effort for the volunteers. (I was asked about this on a separate thread).

         

        I don't have time to prepare a full view, however please do know that table calculations can be independently sorted via their addressing so in this case I'd set up individual table calculations for each concatenation with their own sort, these would be concatenated feeder calculations:

         

        IF FIRST()==0 THEN

             MIN([Field])

        ELSEIF MIN([Field]) != LOOKUP(MIN([Field]),-1) THEN

           PREVIOUS_VALUE('')+ ', ' + MIN([Field])

        ELSE

           PREVIOUS_VALUE('')

        END

         

        Note that this is different from @kettan's calc in a few ways, this should have much higher performance because it's not using ATTR() and avoids the potential issues with CONTAINS().

         

        Then to propagate the last value for each calculation there would be a field that has the same addressing:

         

        PREVIOUS_VALUE(LOOKUP([my concatenated feeder]),LAST())

         

        Finally there would be either a FIRST()==0 or LAST()==0 or INDEX()==1 filter to get rid of the extraneous detail.

         

        Jonathan

        2 of 2 people found this helpful
        • 16. Re: How can i use a unique-concatenate aggregation to a string field?
          Shawn Wallwork

          Tableau you do get that this is a perfectly workable answer. An answer using 4-5-6 table calculations with addressing & partitioning set perfectly (or not). And may or may not actually be usable (understandable) by the OP.

           

          This might work perfectly fine. But for mere mortals I wonder; just a commentary.

           

          --Shawn

          • 17. Re: How can i use a unique-concatenate aggregation to a string field?
            Jonathan Drummey

            Hi Manu,

             

            I have some time to expand on my earlier notes. Here's a solution entirely in Tableau (note that the numbers are different than in the posted Expected Outcome Excel sheet because several values are wrong in that sheet, for example in some places it was summing price instead of volume for the sum of volume).

             

            Screen Shot 2016-08-08 at 8.17.27 PM.png

             

            Given the complexity of the view (it uses nested table calculations, nested Level of Detail Expressions, nested table calculations built on top of nested level of Detail Expressions, and at least two techniques not covered in standard Tableau trainings) this is somewhere deep into the "f-ing advanced" territory of working with Tableau. To give a little more context of what I mean here's the workout view I used to verify the calculations for the above view, there's a ton of calculations involved in making this happen:

             

            Screen Shot 2016-08-08 at 8.34.16 PM.png

             

             

            While I can build a view like this for fun (I'm strange that way) and show it to you as a proof of concept, in my daily work I try very hard to avoid building views like this because they require rarified Tableau knowledge to maintain and can create other difficulties. For example, I built this using FIXED Level of Detail Expressions, and in another post in this thread you'd mentioned having a number of filters. Those filters would all need to be context filters in order to affect the view. Also, you'd mentioned having 100K rows, given the number of calculations involved and not knowing your actual data source I don't have a good sense of whether this will have the responsiveness you want.

             

            When I see a requested view like this that is a bunch of text fields and a statement about using lots of filters my first thought is that someone has had a view like this in Excel or some other tool and is trying to recreate it in Tableau. This is not a good way to start using Tableau because Tableau is not Excel or a slightly more advanced version of Excel, it's a different tool that works with data in its own way. So some things that are easy to do in your old BI tool are harder to do in Tableau and vice versa, and you're better off finding out what's easy to do in Tableau before you head down the path of trying to replicate some exploratory text table dashboard built in an old-school BI tool.

             

            For example, if you really need filters my suggestion would be to create one or more "filter sheets" to use as the origin of Filter Actions to drill down, then have a separate worksheet to display the measures per Animal that has a Filter Action to display three additional worksheets for the lists of Countries, Trees, and Fruit per Animal. The measures per Animal would get a lot simpler to build, the three lists would get a lot simpler to build, the navigation would be fast, and the whole thing would be easy to maintain.

             

            And I know sometimes we don't get paid unless we do exactly what our client/boss/user wants, so here's a quick explanation of how I built the above view, to fully understand this you're going to need to follow the links in http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/ and Level of Detail (LOD) Expressions | Drawing with Numbers and dedicate some quality time to learning about these two powerful (and powerfully complicated) aspects of Tableau.

             

            - The view requires not only Animal but also Fruits, Country, and Tree as dimensions in order to build the concatenated string lists (Fruits is required because there could be ties for the most common fruit). This is because the only way to build a concatenated string list across rows in Tableau is to use table calculations, and for the table calculations to work they need all the necessary dimensions in the view. Because there are multiple string concatenations this makes those calculations more complicated to set up and because of the increased level of detail of the view that means that regular aggregations like SUM(Volume) won't work anymore and we need to resort to other means (such as additional table calculations).

             

            - The Country and Tree concatenated string lists uses nested table calculations per my earlier post in this thread Re: How can i use a unique-concatenate aggregation to a string field?, this is based on #15 in Top 10 Table Calculations – The Next N, Where N >= 15 | Drawing with Numbers . Note that because of the view needing other string lists I'm using the PREVIOUS_VALUE(WINDOW_MAX([field concat])) to "pad out" the string list to every address. The key to sorting the lists in the right order is that for the given list that dimension (e.g. Country) is first in the addressing and then the other dimensions that aren't partitioning (Fruits and Tree in the case of Country) are after that:

             

            Screen Shot 2016-08-08 at 8.47.56 PM.png

             

            - The Fruits list is different because it's a list of the most common Fruit based on the # of records per Fruit for that Animal. So the list is based not on values, but on a measure. I used a variation on the technique described in LOD Expression Remix – Finding a Dimension at a Lower Level | Drawing with Numbers to find the most common fruits in each Animal and then put that inside nested table calculations to concatenate the strings.

             

            - The most common price measure uses another set of nested LOD expressions using the above technique to find the most common price. Note that this is set up to just return the maximum value of the most common values of Price if there's a tie for the most common price.

             

            - Because the viz level of detail includes Fruits, Country, and Tree we can't just use SUM(Volume) or AVG(Price), in both cases I wrapped those with the TOTAL() aggregation and set the compute using to an Advanced on Country, Fruits, and Tree.

             

            - Also because the viz level of detail includes the extra dimensions and we really only want to show one row per Animal I set up a FIRST()==0 table calculation to use as a filter, this also has an Advanced addressing.

             

            - Finally after duplicating the workout worksheet and removing unneeded pills to build the view I turned off Show Header and tooltips for the Fruits, Country, and Tree dimensions.

             

            Jonathan

            3 of 3 people found this helpful
            • 18. Re: How can i use a unique-concatenate aggregation to a string field?
              Tom W

              Jonathan Drummey your dedication and level of detail never ceases to amaze me...

              • 19. Re: How can i use a unique-concatenate aggregation to a string field?

                Jonathan Drummey  Thanks a lot for spending time on this.

                you are right Jonathan as its a conversion from another BI tool to tableau.but also to note that there is a well explained explanation is there to use this kind of visuals and its a starting places of the flow of the analysis . so its kind of unavoidable in that sense.i am already using a lot of FIXED LoD calculations in my workbook to arrive at this table layout (even though knowing that these are performance hindering calculation as it uses inner joins to arrive at results ) and all my filters are context filters.  talking about my actual visual i has 3 fields using most common , 4 fields using concatenate and 3 normally aggregated fields.

                 

                Currently i am using version 9.0 of tableau hence i am not able to open the file you sent, i need to upgrade the version to have a look on that workbook. but according to the  screenshots i think you have calculated all based on the [Animal] field instead of [ID] field.i know its the calculations that matters.

                 

                Thanks a lot for helping me. let me go through that workbook..

                • 20. Re: How can i use a unique-concatenate aggregation to a string field?
                  kettan

                  Currently i am using version 9.0 of tableau hence i am not able to open the file you sent, i need to upgrade the version to have a look on that workbook ...

                  I also have 9.0 but could open it after changing 9.3 to 9.0 in the twbx's twb file in a text editor (see screenshot below) after first adding the suffix .zip to the twbx's filename. More info about this technique can be found under Resources in FAQ:  16+ Columns.

                   

                  The modified file is attached and can be opened with Tableau 9.0.

                   

                  1 of 1 people found this helpful
                  • 21. Re: How can i use a unique-concatenate aggregation to a string field?
                    Jonathan Drummey

                    Hi Manu,

                     

                    I *strongly* urge you to upgrade from Tableau 9.0. LODs were new in v9.0 and there have been at least a couple of bugs that have been fixed in later releases (though not necessarily documented in the release notes because Tableau has a habit of not documenting all changes in the release notes). Here are two in particular:

                     

                    - In older versions when I have a lot of LODs in a view working at multiple levels of detail sometimes Tableau wouldn't be able to execute the query it generated, this goes away when I open the workbook in a newer version. For example I first started building the workbook in v9.2.6 using EXCLUDE LODs and ran into this problem.

                    - There was a bug in earlier 9.x versions where a filter on a FIXED LOD was applied at the same time as other record level filters and not with the appropriate precedence in the order of operations, this was fixed somewhere in the 9.3.x versions.

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 22. Re: How can i use a unique-concatenate aggregation to a string field?
                      Jonathan Drummey

                      Hi Tom,

                       

                      Thanks!

                       

                      re: dedication: It's a combination of wanting to help people and sheer stubbornness.

                       

                      re: level of detail: A lot of what I have learned about Tableau comes from paying close attention to what Tableau is doing and then where there is a behavior that I don't understand or there's not a good explanation I go and explore that area until I do have a sufficient understanding. That's a large reason why I urge forum responders to not just copy in links to KB articles or other posts as answers but actually apply that to the questioner's data & situation. Besides ending up with a more accurate answer there's a ton of learning that comes from seeing Tableau in action as we are dragging and dropping pills, setting options, etc.

                       

                      Jonathan

                      1 2 Previous Next