10 Replies Latest reply on Feb 3, 2020 11:33 AM by Bryce Larsen

    Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail

    Kyle Chalmers

      Hello Tableau Community!

       

      So I've looked far and wide for an answer to this question, but I have not come across the exact answer I'm looking for. My data looks like the following:

       

      Customer IDEvent Type

      123

      Event B
      123Event E
      123Event B
      123Event C
      234Event A
      234Event A
      345Event D
      456Event D
      456Event E
      567Event A
      567Event C
      567Event A
      678Event D
      678Event E
      789Event A
      789Event C
      890Event E
      890Event D
      890Event D

       

      What I need to do is count the number of Customer IDs by the distinct combination of Event Types that puts the Event Types in alphabetical order. So when I create the calculated field or aggregation, the output I'm looking for should be this:

       

      Number of Customer IDsUnique Combination of Event Type
      1Event B, Event C, Event E
      1Event A
      1Event D
      2Event A, Event C
      3Event D, Event E

       

      The calculated field would concatenate Event B, Event C, Event E into one row and count 1 (Customer ID 123); Event A into one row and count 1 (Customer ID 234); Event D into one row and count 1 (Customer ID 345); Event A, Event C into one row and count 2 (Customer ID 456 and 789); and Event D, Event E into one row and count 3 (Customer ID 567, 678, and 890).

       

      I've thought that I can maybe do this using a Level of Detail expression incorporating PREVIOUS_VALUE(), LOOKUP(), or something else, but I have not been successful in my attempts. Can anyone help?

       

      I've looked at the following articles for reference:

       

      Thank you for your future help!

        • 1. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
          Andrew Watson

          Without having an indication of the number of Event Types or the number of occurrences per Customer ID perhaps this can be solved mathematically? Would it be possible to assign, for example, 1 to Type A, 10 to Type B, 100 to Type C? You may be able to sum those values to work out which Event Type combination each customer sits in. For example 11 (1 + 10) would mean Type A and Type B, 101 (1 + 100) would mean Type A and Type C, etc.

           

          The feasibility would depend on the number of event types - too many and the numbers would get too large, and the number of times an event can happen per customer id. For example if it's possible for Type A to occur 11 times then a value returned of 11 could either mean Type A and Type B or 11 Types As.

           

          Note there would be more complex calculations available to get around the summation challenges.

           

          Hope you understand what I mean with the above possibility. Happy to clarify if not clear.

          • 2. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
            Bryce Larsen

            Hi Kyle,

            This was difficult! But I think I ended up getting to what you needed:

            This was achieved using a combination of LOD expressions and Table Calculations. I have a meeting shortly, so I can't go into too much detail at the moment, but wanted to post in case you want to dig in.

            Please note that there is some hidden data (on Max Event Per Customer). It's used during some of the table calculations but ends up needing to be hidden to get the viz you're looking for.

             

            Best,

            Bryce

            1 of 1 people found this helpful
            • 3. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
              Kyle Chalmers

              Hey Andrew,

               

              Thank you for the response! Unfortunately there can be an unlimited number of events per customer. For example, Customer 123 could have 10 Event A rows, 20 Event B rows, and 30 Event C rows. While assigning mathematical values would work if they were limited to having a certain number of events per Event Type, they are not.

               

              Let me know if that makes sense, but I think understood and addressed your suggestion correctly.

              • 4. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
                Kyle Chalmers

                Hey Bryce,

                 

                Thanks for sending this along! This is really helpful. Is there anyway you can post instructions with steps on how you did it? I've replicated the same thing in my internal workbook, but whenever I get down to the table with just [Customer Running Count] and [Events], it only shows me the [Customer Running Count] as being equal to 1, when I know there are more values than that. I'm not sure if order matters when you built out and applied these different calculated fields and table calculations.

                 

                Thanks!

                 

                Kyle

                • 5. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
                  Bryce Larsen

                  Hi Kyle,

                  Do you have all the necessary fields on your detail pane still?

                  Here are the steps I went through (after failing several other ways I must say!).

                   

                  1. Calculate total events per Customer (Events Per Customer LOD)
                    • {fixed [Customer ID]: COUNTD([Event Type])}
                  2. Calculate the last/max event per Customer (Max Event Per Customer)
                    • {fixed [Customer ID], [Event Type]: MAX([Event Type] = {fixed [Customer ID]: MAX([Event Type])})}
                  3. Drag all of these onto a worksheet.
                  4. Create the concatenated Event Type field (Events)
                    • IF FIRST()=0 THEN MIN([Event Type]) ELSE PREVIOUS_VALUE(MIN([Event Type])) + ", " + MIN([Event Type]) END
                    • Partition it by Customer ID
                  5. Create the First Customer of Events field.
                    • IF MAX([Max Event Per Customer]) THEN FIRST()=0 END
                    • This is partitioned by everything except Customer ID.
                  6. Then I made the Customer Running Count field
                  7. And I added a Last Customer Per Event field in order to hide unnecessary rows
                  8. Similar, I right clicked on False under Max Event Per Customer and selected 'Hide' to clean it up.

                   

                   

                  Now...having said all of that...there's an error with this approach I've just confirmed. I added a Customer, 999, with Events A, C, and E and this then returns the wrong count...

                   

                  I'll have to try something else.

                  • 6. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
                    Bryce Larsen

                    OK!

                    I'm back. Steps 1-4 are the same.

                    1. Calculate total events per Customer (Events Per Customer LOD)
                      • {fixed [Customer ID]: COUNTD([Event Type])}
                    2. Calculate the last/max event per Customer (Max Event Per Customer)
                      • {fixed [Customer ID], [Event Type]: MAX([Event Type] = {fixed [Customer ID]: MAX([Event Type])})}
                    3. Drag all of these onto a worksheet.
                    4. Create the concatenated Event Type field (Events). Partitioned by Customer ID (everything except Customer ID checked).
                      • IF FIRST()=0 THEN MIN([Event Type]) ELSE PREVIOUS_VALUE(MIN([Event Type])) + ", " + MIN([Event Type]) END
                    5. Create a Window Calc (Events per Customer TC). This is also Partitioned by Customer ID.
                      • WINDOW_MAX([Events])
                    6. Create a flag for a single Customer Event String
                    7. Next, create the # Customers field
                      • IF [Events per Customer TC]<>LOOKUP([Events per Customer TC],-1) OR ISNULL(LOOKUP([Events per Customer TC],-1)) THEN 1 ELSE PREVIOUS_VALUE(SUM(1))+1 END
                    8. Lastly, I made a flag for which rows to keep:
                      • [Events per Customer TC]<>LOOKUP([Events per Customer TC],1) OR LAST()=0
                      • This uses every Dimension (all of them checked)

                     

                    Final result:

                    You can see this now shows the combination of Event A, C, and E for Customer 999 that I added. Again, please note that I right clicked on Max Event Per Customer and hid the False rows.

                     

                    Best,

                    Bryce

                    • 7. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
                      Kyle Chalmers

                      Hey Bryce Larsen

                       

                      Thanks so much for all of this, this is incredible work that you have put together and you clearly have put a tremendous amount of thought into it.

                       

                      While I see it working for this dataset, I'm having trouble with getting it to work for the larger more complex datasets I am working with.

                       

                      I've attached a scrubbed example of what I'm working with, so I'm hoping you can take a look at it to see if you can get it to work for you, as I'm nailing a few steps but then am struggling with the [# Customers] and [Rows to Keep]. Additionally, do you use the [Single Customer Flag] field? I know you mentioned to build that, but I don't see it included on the visuals in your workbook. Check out the attachment to this reply for a larger a dataset to see if you can solve the problem for that too. I'm not sure why it is struggling with this large of a dataset as this process you built out should scale.

                       

                      Thanks so much for all of your hard work already too - can't tell you how educational and helpful this has been.

                       

                      Best,

                       

                      Kyle

                      • 8. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
                        Bryce Larsen

                        Definitely proving tricky! Out of curiosity, what's your data source?

                        I'm just curious if it's easier doing some of this at the data source layer unless there's a reason you need this dynamic (date filters?).

                         

                        Best,

                        Bryce

                        • 9. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
                          Kyle Chalmers

                          My data source is an Amazon Athena database where I'm using Custom SQL to pull it. Ideally, I could be able to filter on this data using dates for when the events happened too. There is a date column I'm not including that is to the right of that data - I can include it if you think it would help?

                          • 10. Re: Concatenating Unique Text Values in Different Rows for a Dimension at a Higher Level of Detail
                            Bryce Larsen

                            Gotcha. I just wanted to confirm - always easier to concatenate at data source if possible, but not if you want it dynamic (unless it was a stored procedure or something).

                            I've shared this to twitter as well in case others find time to help out before I can!