Good morning - I am a new Alteryx user and have spent hours this weekend trying to find the solution to this problem, but couldn't quite find the solution in the community posts, or the academy so am hoping I can ask for help here. Currently: I have transposed a sample rows of data, joined them and now have them in this format: Patient MPIAdmitYearAdmitMonthDischargeYearDischargeMonthLengthOfStayPlaceOfService1317,19,18,194,11,6,1117,19,18,194,11,6,115,6,1,9IP-Acute,IP-Acute,IP-Acute,IP-Acute What i would now like to do is to separate out the cells into distinct columns that would match the number of items in the cell (attached mock up of what I want the data to look like) so I can perform calculations on it. I have attached how i would like to see the data in the attached excel sheet. Thank you for your help - I really appreciate it! Data Joined Current_Future State.xlsx

Joining Row Data into separate columns

Good morning - I am a new Alteryx user and have spent hours this weekend trying to find the solution to this problem, but couldn't quite find the solution in the community posts, or the academy so am hoping I can ask for help here.

Currently:

I have transposed a sample rows of data, joined them and now have them in this format:

Patient MPI	AdmitYear	AdmitMonth	DischargeYear	DischargeMonth	LengthOfStay	PlaceOfService
13	17,19,18,19	4,11,6,11	17,19,18,19	4,11,6,11	5,6,1,9	IP-Acute,IP-Acute,IP-Acute,IP-Acute

What i would now like to do is to separate out the cells into distinct columns that would match the number of items in the cell (attached mock up of what I want the data to look like) so I can perform calculations on it. I have attached how i would like to see the data in the attached excel sheet.

Thank you for your help - I really appreciate it!

Data Joined Current_Future State.xlsx

Preparation

Accepted answers

Christine_Assaad

Hi @jsluchsinger

Does the below help?

Data on same row.yxmd

All comments

AngelosPachis

Hi @jsluchsinger ,

Welcome to the community. Is there a particular reason why we need our data to be in a single row?

I would advise against creating a single row with multiple columns having the same name + an Identifier (in this case 1-4), since it takes more space and it's maybe more difficult to handle. Instead, you could achieve the same if you have a single row for each one of your categories as shown below

Screenshot 2020-11-10 174630.jpg

Would that work in your occasion, or should that be pivoted necessarily into a single row?

Regards,

Angelos

example.yxmd

jsluchsinger

Thank you so much for your comments - the data was in the format you noted prior to my transforming it - I need to perform a calculation on the data and thought that was the best way to look at the data - but based on your note, maybe not!

My ultimate goal is to write a formula that uses the data to calculate a time difference - so time between the first occurrence (year 1, month 1 admit and year1, month 1 discharge) and the next occurrence (year2, month2 admits and year2, month2 discharges)..but i cannot use the normal date/time because i do not have the specific date - just month and year.

I need to be able to calculate:

	By Place of Service	First Admit Date	Last Discharge Date	Readmission Rate per 30 day period
Patient MPI

Do you have thoughts on how to do that using multiple rows? I want to do the calculation/formula for each of the unique MPIs so that i can get an overall readmission monthly rate by patient.

Thank you for helping me think this thru!

Christine_Assaad

Hi @jsluchsinger

Does the below help?

Data on same row.yxmd

jsluchsinger

Thank you - that worked! This will allow me to create additional calculations too!

Thanks - great first experience posting on the community site!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335