6 Replies Latest reply on Mar 25, 2019 9:19 AM by Jody Gastrich

    Dynamic matching

    Angela Rachubinski

      Hello!

      I’d like to be able to match individuals from Cohort A with individuals from Cohort B, based on gender and closest age match (+/- 5 years). Each individual in Cohort B can only be matched with one person in Cohort A ('Original' example sheet in attached workbook). These pairings will shift as we continually add more individuals into the database (see 'Expanded' example sheet in the workbook with expected matches).

       

      Is there a way to write this as a calculated field in Tableau? Right now, all I need is a boring output table as a product. Or is this something that would have to be pre-processed in R or Python?

       

      Thanks in advance for any suggestions!

       

      Angela

      Tableau 2018.2

        • 1. Re: Dynamic matching
          Ken Flerlage

          Hmm, you're talking about fuzzy matching here I think. Unfortunately, I don't know of a way to do that directly in Tableau. You're probably going to need to look at doing this within your data prep. Various data prep and ETL tools, such as Alteryx, should be able to do this. Or you could write some code.

          • 2. Re: Dynamic matching
            Shinichiro Murakami

            Hi Angela

            Are you able to attach original excel file?

            This requires modifying connection which needs original excel? file.

             

            Thanks,

            Shin

            • 3. Re: Dynamic matching
              swaroop.gantela

              Angela,

               

              I was able to recreate your match table using a combination of

              self-joins and calculated fields, but this will likely show poor performance

              with large datasets. Better performance, as Ken stated above, will

              likely be achieved with data prep.

               

              Nonetheless, please see workbook v2018.1 attached in the Forum Thread.

              Can explain further, if desired.

              • 4. Re: Dynamic matching
                Jody Gastrich

                Hi Swaroop,

                I'm excited to see your calcs, etc, but unfortunately, I can't open your file (I have earlier version and can't upgrade until pushed to me... company policy).

                If it's not too much trouble and if it's still understandable, would you be able to post the calculated fields, instead of the whole workbook?

                I know it's not ideal, but this is the closest thread to what I'm trying to accomplish and interested in solution.

                Thanks!

                • 5. Re: Dynamic matching
                  swaroop.gantela

                  Jody,

                   

                  I've attached here the workbook as v10.2.

                   

                  Basically, I joined the dataset to itself (likely performance hit) as shown below.

                  There may some other criteria that you can add to the join to limit it further.


                  Then I calculated the age difference as:

                  ABS([Age]-[Age (Sheet11)])

                   

                  And then used an LOD to find the minimum age difference

                  (along with some other conditionals, not same cohort etc.)

                   

                  { FIXED [Study ID]:MAX(
                  IF [AgeDifference]={ FIXED [Study ID]: MIN([AgeDifference])}
                      AND [AgeDifference]<=5
                      AND [Cohort]<>[Cohort (Sheet11)]
                      AND [Cohort]="A"
                      THEN [Study ID (Sheet11)]
                  ELSE "None"
                  END)}

                   

                  Please feel free to post a separate question and tag me on it,

                  would be happy to try to assist.

                   

                   

                  278689matchJoin.png

                  1 of 1 people found this helpful
                  • 6. Re: Dynamic matching
                    Jody Gastrich

                    Swaroop,

                    Thanks again! Yes, I can open it now!

                    It will take a bit for me to think how I can apply to my data, but it's a great start.