topic Re: Combine multiple rows into one row in Alteryx Designer Desktop Discussions

Combine multiple rows into one row

cebiddle — Mon, 04 Feb 2019 18:45:54 GMT

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

Re: Combine multiple rows into one row

estherb47 — Mon, 04 Feb 2019 19:04:59 GMT

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

Re: Combine multiple rows into one row

HomesickSurfer — Mon, 04 Feb 2019 20:46:36 GMT

Hi @cibiddle

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

Jerry

Re: Combine multiple rows into one row

ponraj — Mon, 04 Feb 2019 21:07:06 GMT

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