Combine multiple rows into one row

[cebiddle]

Person_ID	Visit_ID	DX1	DX2	DX3	DX4
1	1	1	0	0	0
1	1	0	1	0	0
1	1	0	0	0	1
1	1	0	0	1	0
1	2	1	0	0	0
2	3	1	0	0	0
2	3	0	0	1	0
3	4	0	1	0	0
4	5	1	0	0	0

Hello, I am trying to create a dataset that only has one row per visit id. Currently, my data is structured as above, where each row contains a diagnosis indicator for each visit. What I would like is to have one row for each visit with all the dx. So it would look like this: 

Person_ID	Visit_ID	DX1	DX2	DX3	DX4
1	1	1	1	1	1
1	2	1	0	0	0
2	3	1	0	1	0
3	4	0	1	0	0
4	5	1	0	0	0

I am new to alteryx and not sure what the best way to do this would be? I looked at the multi-row function, but I want to look at more than a constant n row/s above or below. 

Thanks

Cait

[estherb47 · Accepted solution]

Hi @cebiddle !

To make it more dynamic, you'll want to do a bit of transposing and summarizing.

Transpose turns the data vertical, and then we can use the summarize tool to find the largest value for each Visit. The crosstab tool turns it back into the table.

Cheers!

Esther

[HomesickSurfer]

Hi @cibiddle

I've achieved the results you seek with a few tools as follows...the Summary tool can be configured accordingly.

Jerry

[ponraj]

Here is the sample workflow for your case. Hope this is helpful.