Join the Alteryx Community’s Maveryx Summer Cup event! Compete, network with others, and earn your gold through a series of challenges from July 24th to August 11th. Learn more about the event here.

Alteryx Designer Desktop Discussions

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

rows to columns - multiple rows into a single row

agumaste
5 - Atom

Hi, I am trying to convert multiple rows into a single with converting some of the elements of a transaction into columns. The data I have is as follows:

 

Patient IDsource_patient_idcase_idreferral_datecase_status_datecase_substatus_reasons_codecase_substatus_reasons_description
1001458255/12/20235/12/2023CNHCP DECISION
10014585287/5/20237/27/2023ACAPPROVED ON THERAPY

 

And I want to convert this into:

 

Patient IDsource_patient_idreferral_date_1referr_date_2case_status_date_1case_status_date_2Status_reason_code_1Status_reason_code_2diff_ref_datefinal_disposition
10014585/12/20237/5/20235/12/20237/27/2023CNAC54APPROVED ON THERAPY

 

I have total 30 unique Ids for which I need to do this. Only for one Id I have three transactions, rest all I have two transactions per ID. 

 

Could you please advise on how I can achieve this?

 

Thanks! 

3 REPLIES 3
Peachyco
11 - Bolide

I would use the Multi-Row Formula tool to assign row ID/counts to each patient - this would allow me to tag the numeric prefixes to the cross-tabbed columns. Then, I'd use Cross Tab and Summarize tools, all of them grouping by Patient ID and Source Patient ID so I can join all of them together in the end. Like so:

Alteryx - agumaste rows to columns a.png

My starting dataset:

Alteryx - agumaste rows to columns b.png

 

My final dataset:

Alteryx - agumaste rows to columns c.png

 

binuacs
20 - Arcturus

@agumaste One way of doing this

image.png

flying008
14 - Magnetar

Hi, @agumaste 

 

Another way like @binuacs :

 

录制_2023_09_14_16_35_17_545.gif

Labels