Transpose Columns to Rows and find values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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,
