Data Manipulation Issue

[oswinhull]

Hello Alteryx community,

I was wondering if anybody on here could help me figure this out. 

Basically I have a data set like this:

ID1	DATE	STATE	LAT	LONG	AGE	GENDER
461105	41275	Pennsylvania	40.3467	-79.8559	0::20	0::Male||1::Male||3::Male||4::Female
460726	41275	California	33.909	-118.333	0::20	0::Male
478855	41275	Ohio	41.4455	-82.1377	0::25||1::31||2::33||3::34||4::33	0::Male||1::Male||2::Male||3::Male||4::Male
478925	41279	Colorado	39.6518	-104.802	0::29||1::33||2::56||3::33	0::Female||1::Male||2::Male||3::Male
478959	41281	North Carolina	36.114	-79.9569	0::18||1::46||2::14||3::47	0::Female||1::Male||2::Male||3::Female
478948	41281	Oklahoma	36.2405	-95.9768	0::23||1::23||2::33||3::55	0::Female||1::Female||2::Female||3::Female||4::Male||5::Male
479363	41293	New Mexico	34.9791	-106.716	0::51||1::40||2::9||3::5||4::2||5::15	0::Male||1::Female||2::Male||3::Female||4::Female||5::Male

I need it to become like this:

ID1	ID2	GENDER	AGE
461105	0	Male	20
461105	1	Male
461105	2
461105	3	Male
461105	4	Female
460726	0	Male	20
478855	0	Male	25
478855	1	Male	31
478855	2	Male	33
478855	3	Male	34
478855	4	Male	33

etc..

I have never seen data structured in this way before. I don't know if its a common format I'm just not aware of. Is there an easy way to do this? Any help would be appreciated.

Thanks.

[NicoleJohnson · Accepted solution]

You should be able to do this with a few instances of the Text to Columns tool - my recommendation would be to first replace all of the double delimiter characters with single characters in the Age & Gender fields (i.e. || becomes | and :: becomes :). Then, using the Text to Column tool, Split your Age into rows for | delimiter, then Text to Column again splitting Gender into columns with the : delimiter... repeat the process in a separate stream for Gender... then use Join & Union with some data cleanup to get your data all back together in one list. 

If you need to show the blanks for ID2 that didn't have an age or gender specified, you can do so by determining the min & max ID2 for each ID1... then Generate Rows for every number between the two... then join this list back to the data from above, and union any leftover ID1/ID2 combinations to create the full list.

I've attached a sample workflow - hope this helps get you pointed in the right direction! :)

Cheers,

NJ

[oswinhull]

I was kind of hoping there was an easier way to do it. Apparently not.