6 Replies Latest reply on Dec 7, 2018 12:16 PM by Rachel Tobin

    Finding similar string values

    Rachel Tobin

      Hello all,

       

      I have an array of 5 attributes for employees that I would like to compare to other employees.

       

      Examples:

      Employee      AttributeArray

      1234               Happy / Funny / Silly / Sad / Angry

      2345               Funny / Silly / Sad / Tired / Strong

      3456               Angry / Happy / Silly / Funny / Sad

       

      My end goal is to be able to select an employee, and see a count of how many other employees have the same attributes (in any order). For the example, if I selected employee 1234, I'd see 1 other employee (3456) as the result. For 2345, I'd see 0. I'd like to get a count, as well as be able to show who those other employees are.

       

      There are 34 unique attributes that can appear in any order for an employee. I do have all of the attributes individually, too (Employee, AttributeArray, Attribute1, Attribute2, Attruibute3...etc)

       

      Any ideas?

       

      I'm using 2018.1.6

        • 1. Re: Finding similar string values
          Don Wise

          Hi Rachel,

          Hard to say how this would play out unless there were more attributes/data provided, but to get you started and point you in the right direction (others may have other ideas too...) you could do something like this, using this type of calc for each attribute then bringing all attributes into a parameter or secondary calc to filter off of:

           

          IF CONTAINS([Attribute Array], 'Happy')

          AND CONTAINS([Attribute Array], 'Funny')

          AND CONTAINS([Attribute Array], 'Angry')

          AND CONTAINS([Attribute Array], 'Silly')   

          AND CONTAINS([Attribute Array], 'Sad')

          THEN 'Show' ELSE 'Hide' END

           

          Screen Shot 2018-12-07 at 11.21.26 AM.png

           

          Hope it helps! Thx, Don

          • 2. Re: Finding similar string values
            Jennifer VonHagel

            Hi Rachel,

             

            Do your Attribute 1, Attribute 2, etc fields have different values in them, or are they consistently the same value? Table 1 or Table 2 below?

             

            In either case, I'd create a 34-character Attribute Flag field in which each character is a 0 or 1 depending on whether the employee has that Attribute.  The Attributes of course must be in the same order in every string:

             

            If your table is like Table 1 above, you can create the field like this (though of course your field will 34 rather than 5 characters).

             

            If your table is like Table 2 above, you'll need to reference the Attribute values themselves to create a calculation:

             

            Ok, now we have a field by which we can easily compare employees with exactly the same attributes.

             

            I think for the next part you will need to use a parameter to select the Employee, rather than the Employee field as a filter. This has limitations: you can only select one employee at a time, and if new employees come into the data you will need to manually add them to the parameter.

             

            Create the Employee Parameter:

             

            And then create the Filter which will show all data in which the AttributeFlag2 of the selected Employee is present in the data:

            Put the filter on the Filter shelf and set it to True:

             

            Workbook is attached, I hope this helps.

             

            Best,

            Jennifer

            3 of 3 people found this helpful
            • 3. Re: Finding similar string values
              Daniel Stanish

              Good day Rachel.

               

              I'd suggest normalizing your list. One way to do that is to convert the data into a tabbed form. I took your data, and replaced the ' / ' with tabs to make a tab separated format:

               

              Employee
              1234HappyFunnySillySadAngry
              2345FunnySillySadTiredStrong
              3456AngryHappySillyFunnySad

               

              Once in this form, use Tableau Desktop or Tableau Prep to pivot the data, so you have a new data file that looks like this:

               

              Save to CSV or a Tableau extract. Next, create a new workbook using that as a data source. Add the table, then create a join to the same table again against the attributes.

               

               

              Next build up a view. The relationship you describe will be true if the associated person is someone different, and has 5 related attributes. To do that, create a calculation that checks if the related person is different, and filter on that. Add another filter for only those relationships with 5 matching attributes.

               

              Sample is attached. Does this help?

               

              Best regards,

               

              -Dan

              2 of 2 people found this helpful
              • 4. Re: Finding similar string values
                Rachel Tobin

                This seems to be working perfectly! Thank you.

                 

                For others following this...because I had 34 attributes and didn't exactly want to type them all out, I copied all the attributes to excel, typed one instance of the calculated field, and then flash filled the rest. I'm sure most people know how to do this, but it was a life saver for me when I learned it

                 

                Flash fill fills in all additional ones for me...

                Copy and paste all of column b into your calculated field, delete the last "+", and you're done.

                 

                 

                Thanks again for the easy step-by-step, Jennifer!

                • 5. Re: Finding similar string values
                  Jennifer VonHagel

                  You're welcome!

                   

                  Credit where due... I once worked with bank data in which customer permissions and attributes were stored as a string of 0|1 flags. Concise and easy to work with!

                   

                  Best,

                  Jennifer 

                  • 6. Re: Finding similar string values
                    Rachel Tobin

                    This also worked well! Thanks! This gives me the freedom to also do it by "sorta similar" where 4/5 attributes match (or 3/5, etc).