Hi, Beginner here. I have data strings in 2 columns separated by delimiters: COLUMN ACOLUMN BA,B,C,D,E1,,1,1,1B,D,E,,2C,D1,1A,C,D,E1,3,2, I'm looking to have the following output. COLUMN 1COLUMN 2COLUMN 3COLUMN 4COLUMN 5A-1B-C-1D-1E-1 B- D-E-2 C-1D-1 A-1 C-3D-2E- Just looking the first few steps as I'm drawing a blank. Thanks.

Combine Data Pairs from Multiple Columns with Delimiters

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.

Workflow

Parse

Help

Accepted answers

KarolinaRoza

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

combine pairs.yxmd

All comments

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

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

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

combine pairs.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703