Combine Data Pairs from Multiple Columns with Delimiters

[nat0028]

Hi,

Beginner here.  I have data strings in 2 columns separated by delimiters:

COLUMN A	COLUMN B
A,B,C,D,E	1,,1,1,1
B,D,E	,,2
C,D	1,1
A,C,D,E	1,3,2,

I'm looking to have the following output.

COLUMN 1	COLUMN 2	COLUMN 3	COLUMN 4	COLUMN 5
A-1	B-	C-1	D-1	E-1
	B-		D-	E-2
		C-1	D-1
A-1		C-3	D-2	E-

Just looking the first few steps as I'm drawing a blank.  Thanks.

[Emil_Kos]

Hi @nat0028,

I am not sure if you can accomplish that. 

Let's look at the second line there is BDE and ,,2 There is no indication that it should go to the column second fourth and five. 

Unless there is some kind of pattern here but I don't see it here.

[KarolinaRoza]

Hi,

@Emil_Kos i see the pattern 😉 but my question is: @nat0028: do you always have data like A, B, C.. which goes to first, second, third etc columns in desired results?

Or is it sample dataset? And you have real strings, i mean words or sentences separated by comma?

In that case it would be little harder to achieve it.

Now I do not have Alteryx open in front of me, but tommorow I should be able to help you unless someone already solve it:-)  

Karolina

[nat0028]

The data in source column A are set.  They're pretty much fixed category names while values in col B can be variable.  So we can replace the values like below.  Hope it makes it clearer.

SOURCE
COLUMN A	COLUMN B
HEIGHT,WEIGHT,GENDER,HOBBIES,SIZE	1,,1,1,1
WEIGHT,HOBBIES,SIZE	,,2
GENDER,HOBBIES	1,1
HEIGHT,GENDER,HOBBIES,SIZE	1,3,2,
RESULT
COLUMN 1	COLUMN 2	COLUMN 3	COLUMN 4	COLUMN 5
HEIGHT-1	WEIGHT-	GENDER-1	HOBBIES-1	SIZE-1
	WEIGHT-		HOBBIES-	SIZE-2
		GENDER-1	HOBBIES-1
HEIGHT-1		GENDER-3	HOBBIES-2	SIZE-

[Emil_Kos]

Hi @nat0028,

The data will always have 4 rows? If not they will always come in the same sequence afterwards? 

[nat0028]

The data can have more rows that the example.  However, the sequence of how the values in Column A come in are fixed, so HEIGHT,WEIGHT,GENDER,HOBBIES,SIZE in this order, except that in some rows, some of them will be missing.  As far as I can tell, there are only 5 possible "headers" as well.  Thanks.

[KarolinaRoza · Accepted solution]

hi @nat0028 ,

Finally I was possible to sit and focus on your case 🙂

I think I was able to find the solution (attached).

Few comments:

- I added extra comma for cases for which Column B ends with comma as in fact there is 'empty' field.

- columns with key words like :HEIGHT,WEIGHT,GENDER,HOBBIES,SIZE are fixed, so I used Text Input tool. I hope it is enough for your exercise, in case you will have more, you can add it to the lst.

-please test it on your side, as i am not sure if I checked all cases, and let me know if it anything must be adjsuted.

Cheers,

Karolina 

[nat0028]

This looks very promising.  I'm going to take it back and test with a larger data set.

[nat0028]

Tested with a larger data set and it worked.  Thanks again.

[KarolinaRoza]

@nat0028 

Great! I am grad that I could help you.

Regards,

Karolina