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

# Filtering and analize

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

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

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

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

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.