Hi everyone! After a lot of parsing I have concatenated data, but I'am missing the final step before its usable. There are over 90 columns that look like this: TrackingDateDataStatusCodeTrueFalse12321.09.2021 15:01,22.09.2021 15:21,23.09.2021 03:56,23.09.2021 08:27,23.09.2021 12:15PARCV,LDTMV,ULFMV,SRTED,DLVRDfalse,false,false,false,false,false23421.09.2021 15:01,22.09.2021 14:18,22.09.2021 23:46,23.09.2021 07:13,23.09.2021 14:24,23.09.2021 15:01,23.09.2021 15:24,04.10.2021 20:28PARCV,LDTMV,ULFMV,SRTED,CNRFC,HNDDE,HLDCC,DLVRDfalse,false,false,false,false,true,false,false,false34521.09.2021 15:01,22.09.2021 15:29,23.09.2021 01:04,23.09.2021 09:41,23.09.2021 12:18PARCV,LDTMV,ULFMV,SRTED,DLVRDfalse,false,false,false,false,false45621.09.2021 15:01,22.09.2021 14:19,23.09.2021 00:45,23.09.2021 08:09,23.09.2021 14:18,23.09.2021 15:36,24.09.2021 09:31,25.09.2021 12:18PARCV,LDTMV,ULFMV,SRTED,CNRFC,HLDCC,HLDCC,DLVRDfalse,false,false,false,false,false,false,false,false56721.09.2021 15:01,22.09.2021 14:16,22.09.2021 22:23,23.09.2021 03:48,23.09.2021 08:15,23.09.2021 13:28PARCV,LDTMV,ULFMV,ULFMV,SRTED,DLVRDtrue,false,false,false,false,false,false And I need to convert them to rows, but it is not known beforehand how many rows each tracking has. Is it really a lot of text to columns tools to split each column one by one to rows and then using an unique tool afer? I hope this explains it already enough, I am trying to get the following expected output: TrackingnumberDateDataStatusCodeTrueFalseAll other columns similarly121.09.2021 15:01PARCVFALSE..122.09.2021 15:21LDTMVFALSE..123.09.2021 03:56ULFMVFALSE..123.09.2021 08:27SRTEDFALSE..123.09.2021 12:15DLVRDFALSE..221.09.2021 15:01PARCVFALSE..222.09.2021 14:18LDTMVFALSE..222.09.2021 23:46ULFMVFALSE..223.09.2021 07:13SRTEDFALSE..223.09.2021 14:24CNRFCFALSE..223.09.2021 15:01HNDDETRUE..223.09.2021 15:24HLDCCFALSE..204.10.2021 20:28DLVRDFALSE..321.09.2021 15:01PARCVFALSE..etcetcetcetcetc

Multiple columns to rows at the same time

Hi everyone!

After a lot of parsing I have concatenated data, but I'am missing the final step before its usable. There are over 90 columns that look like this:

Tracking	DateData	StatusCode	TrueFalse
123	21.09.2021 15:01,22.09.2021 15:21,23.09.2021 03:56,23.09.2021 08:27,23.09.2021 12:15	PARCV,LDTMV,ULFMV,SRTED,DLVRD	false,false,false,false,false,false
234	21.09.2021 15:01,22.09.2021 14:18,22.09.2021 23:46,23.09.2021 07:13,23.09.2021 14:24,23.09.2021 15:01,23.09.2021 15:24,04.10.2021 20:28	PARCV,LDTMV,ULFMV,SRTED,CNRFC,HNDDE,HLDCC,DLVRD	false,false,false,false,false,true,false,false,false
345	21.09.2021 15:01,22.09.2021 15:29,23.09.2021 01:04,23.09.2021 09:41,23.09.2021 12:18	PARCV,LDTMV,ULFMV,SRTED,DLVRD	false,false,false,false,false,false
456	21.09.2021 15:01,22.09.2021 14:19,23.09.2021 00:45,23.09.2021 08:09,23.09.2021 14:18,23.09.2021 15:36,24.09.2021 09:31,25.09.2021 12:18	PARCV,LDTMV,ULFMV,SRTED,CNRFC,HLDCC,HLDCC,DLVRD	false,false,false,false,false,false,false,false,false
567	21.09.2021 15:01,22.09.2021 14:16,22.09.2021 22:23,23.09.2021 03:48,23.09.2021 08:15,23.09.2021 13:28	PARCV,LDTMV,ULFMV,ULFMV,SRTED,DLVRD	true,false,false,false,false,false,false

And I need to convert them to rows, but it is not known beforehand how many rows each tracking has.

Is it really a lot of text to columns tools to split each column one by one to rows and then using an unique tool afer?

I hope this explains it already enough, I am trying to get the following expected output:

Trackingnumber	DateData	StatusCode	TrueFalse	All other columns similarly
1	21.09.2021 15:01	PARCV	FALSE	..
1	22.09.2021 15:21	LDTMV	FALSE	..
1	23.09.2021 03:56	ULFMV	FALSE	..
1	23.09.2021 08:27	SRTED	FALSE	..
1	23.09.2021 12:15	DLVRD	FALSE	..
2	21.09.2021 15:01	PARCV	FALSE	..
2	22.09.2021 14:18	LDTMV	FALSE	..
2	22.09.2021 23:46	ULFMV	FALSE	..
2	23.09.2021 07:13	SRTED	FALSE	..
2	23.09.2021 14:24	CNRFC	FALSE	..
2	23.09.2021 15:01	HNDDE	TRUE	..
2	23.09.2021 15:24	HLDCC	FALSE	..
2	04.10.2021 20:28	DLVRD	FALSE	..
3	21.09.2021 15:01	PARCV	FALSE	..
etc	etc	etc	etc	etc

Workflow

Accepted answers

Maskell_Rascal

Hi @Pingu

Here is how you can do it.

You'll need to flip your data first and then use a Text to Columns tool to split all the value to rows at the same time. Then just flip it back. I added in a couple extra steps on the workflow to clean up some empty values.

Let me know if this works for you.

Cheers!

Phil

Multiple Colums to Row at same time.yxmd

All comments

Maskell_Rascal

Hi @Pingu

Here is how you can do it.

Let me know if this works for you.

Cheers!

Phil

Multiple Colums to Row at same time.yxmd

Pingu

Thanks very much @Maskell_Rascal , with that help I managed to fix it. Was so close yet so far.

Is there a special meaning behind theTile_num -3 or what does it do?

Maskell_Rascal

@Pingu - In this case, it means nothing. Normally it is the assigned tile of the record, so it can be used when you're splitting data into equal sums etc. The Tile Sequence number is the important one here, since I'm using it as a record id for each grouping.

You can learn more about the tool here: https://help.alteryx.com/20213/designer/tile-tool

stefjaro

Hi Phil,

Many thanks for your solution, saved me precious hours!

Stefan

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335