3 Replies Latest reply on Feb 24, 2017 8:21 AM by Marco R

    Filtering and analize

    Marco R

      Hi to all I'm new on tableau.

      I've to analyze a set of patient that can have multiple diagnosis (0:N) associated.

      I want to extract a list of secondary diagnosis associated to patient that have a fixed type of diagnosis .

      How to do?

       

      ex.

      Patient;Diagnosis

      1;A,B,C

      2;A

      3;A,C

      4;E,F,C

       

      Which are the patients with primary diagnosis of type A and which are the secondary diagnosis o these patients?

      Patient: 1,2,3

      Secondary diagnosis:

      B => COUNTD(Patients)=1

      C => COUNTD(Patients)=2

       

      Thanks in advance for the support.

        • 1. Re: Filtering and analize
          Mark Fraser

          Hi Marco

           

          You'll need help with string manipulation.

          functions such as FIND, LEN, MATCH, LEFT, RIGHT, MID, COUNT etc.

          This is a great resource

          >> String Calculations in Tableau - Clearly and Simply

           

          The simplist in your exmaple is simply

          left([diagnosis],1) to always get the first letter - this assumes they are always the same length...

           

          more to follow on the rest...

           

          Cheers

          Mark

          1 of 1 people found this helpful
          • 2. Re: Filtering and analize
            Mark Fraser

            this is a way to count the B, C etc.

            you would need to count them apart...

             

            instead, you could use the CONTAINS function... same outcome.

            what maybe easier is to split the diagnosis string on import (using the ,) then you have them already split for ease of manipulation.

             

            fun problem and quite a few ways to solve, just need to work out whats most efficient

             

            Cheers

            Mark

            • 3. Re: Filtering and analize
              Marco R

              Thank's Mark!

              Maybe my description was not very clear.

              I want to know which are  the patient's diagnosis (distribution) that have a specified principal diagnosis (precondition).

              In reference to the above example, only patients 1,2,3 contribute to result and the distribution of diagnosis are: 1 patient with B diagnosis and 2 patient with C diagnosis.

              The number of different types diagnosis can be very big.

               

              In SQL-like:

               

              select diagnosis,count(distinct patient)

              from patientdiagnosis_table

              where diagnosis<>'A' and patient in

              (

              select patient from patientdiagnosis_table where diagnosis='A'

              )

              Group by diagnosis

               

              I hope that's more explicit.