topic Re: Transform data from one to one to one to many in Alteryx Designer Desktop Discussions

Transform data from one to one to one to many

PHaisten — Thu, 13 Jun 2024 21:31:38 GMT

I am trying to take some data and create a one to many relationship in Alteryx. I have attached some sample data to show what the input is and what I want to output to resemble.

Input:

Desired Output:

Was able to somewhat perform this using Multi-row formula to sequence each ID, then a Crosstab tool was able to line up the data but only for the IDs. All other data points were left off.

Attempted to use a Transpose then concatenate using the Crosstab tool, but I am unable to parse the concatenated data once that is done. Is this possible to have the dataset transformed from one to one into one to many?

Re: Transform data from one to one to one to many

TimN — Fri, 15 Dec 2023 20:44:51 GMT

Maybe something like this...

Re: Transform data from one to one to one to many

PHaisten — Fri, 15 Dec 2023 20:49:05 GMT

I need any related data to the employee ID to be on the same row. This over a larger data set than what I provided, so a filter would not work and it still does not place the related data into one row.

Re: Transform data from one to one to one to many

TimN — Fri, 15 Dec 2023 20:51:54 GMT

If you remove the filter, is that the output you want?

Re: Transform data from one to one to one to many

TimN — Fri, 15 Dec 2023 20:54:29 GMT

oops so sheet2 is what you have (input) but you want it like sheet1?

Re: Transform data from one to one to one to many

TimN — Fri, 15 Dec 2023 20:59:41 GMT

Maybe I missing your point but this looks like it produces as one line per parent...

Re: Transform data from one to one to one to many

PHaisten — Fri, 15 Dec 2023 21:00:08 GMT

Output I am seeing is not all on the same row for each related user. In my output example I have the employee as one row and each dependent under that user is added on to the same row with new columns. Is this possible to perform in alteryx?

Re: Transform data from one to one to one to many

PHaisten — Fri, 15 Dec 2023 21:01:01 GMT

Sheet 1 is the data input. Sheet 2 is the desired output.

Re: Transform data from one to one to one to many

TimN — Fri, 15 Dec 2023 21:08:00 GMT

I think this one will do it.

Re: Transform data from one to one to one to many

PHaisten — Fri, 15 Dec 2023 21:23:08 GMT

The second one is what I am able to get, but the format needs to be each value associated with it must be in its own column. The Desired Output is what I need the data to be in. Once data is concatenated, is there a way to text to columns the data into individual columns?

Re: Transform data from one to one to one to many

TimN — Fri, 15 Dec 2023 21:37:38 GMT

The last one I sent has data in distinct columns without concatenation. If you have a version with concatenation you can look at the Text to columns tool in the Parse folder that can separate into columns

Re: Transform data from one to one to one to many

PHaisten — Fri, 15 Dec 2023 22:10:12 GMT

I was able to separate the data into separate columns using text to columns, just had to use the tool 6 times to separate all of my columns. Used a Select tool after that to order the data correctly. Thank you for your help!