In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Transpose Columns to Rows and find values

monish_chandra
8 - Asteroid

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

 

IDICD10_Diag1POA1ICD10_Diag2POA2...POA49ICD10_Diag50POA50
1A234 - FluYA457N    
2I567 - ColdYS452Y NI204 - Cardiac ArrestY

 

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. 

1 REPLY 1
Thableaus
17 - Castor
17 - Castor

Hi @monish_chandra 

 

See solution below:

 

SolutionTrans.PNG

- 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,

Labels
Top Solution Authors