Transpose Columns to Rows and find values

[monish_chandra]

I have a Diagnosis table like shown below (just an eg) There are 50 ICD10 columns as shown below.

ID	ICD10_Diag1	POA1	ICD10_Diag2	POA2	...	POA49	ICD10_Diag50	POA50
1	A234 - Flu	Y	A457	N
2	I567 - Cold	Y	S452	Y		N	I204 - Cardiac Arrest	Y

I'm trying to see how I can transpose this table structure so that I can perform the below operation.

Find the records which contain these Diagnosis codes(A234, F384, I567) and their corresponding POA is 'Y'. Even if a single match is found then then ignore the rest of the diagnosis even if there is a match.

I've attached a sample workflow. I'm using a transpose, regex operation to separate colNames and CrossTab to change the orientation. But i'm not able to proceed further than this step. 

[Thableaus · Accepted solution]

Hi @monish_chandra 

See solution below:

- Transpose like you did

- Create a Column ID based on the Diag1, POA1, etc..

- Separates POA from Diag

- Join them together side by side in new columns

- Filter according to your conditions

- Cross-Tab Diag and POA separated, bringing the first record (first match, like you mentioned, it's important to bring only one).

- Join the two columns again

WF attached.

Cheers,