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 ID | source_patient_id | case_id | referral_date | case_status_date | case_substatus_reasons_code | case_substatus_reasons_description |
10014 | 58 | 25 | 5/12/2023 | 5/12/2023 | CN | HCP DECISION |
10014 | 58 | 528 | 7/5/2023 | 7/27/2023 | AC | APPROVED ON THERAPY |
And I want to convert this into:
Patient ID | source_patient_id | referral_date_1 | referr_date_2 | case_status_date_1 | case_status_date_2 | Status_reason_code_1 | Status_reason_code_2 | diff_ref_date | final_disposition |
10014 | 58 | 5/12/2023 | 7/5/2023 | 5/12/2023 | 7/27/2023 | CN | AC | 54 | APPROVED 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!
Solved! Go to Solution.
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:
My starting dataset:
My final dataset:
@agumaste One way of doing this
User | Count |
---|---|
19 | |
15 | |
13 | |
9 | |
8 |