Hi I have a problem separating data stuck in one column

You can try this workflow. I am splitting to rows get seq number using tile number which is converted as column name.

Hope this helps : )

split data (2).yxmd

Hi atcodedog05

I have one more follow on doubt is it possible to concatenate Headers 3,4,5,6,7,..... dynamic number of headers changes every time based on file input as discussed earlier post

while doing concatenatination keep header 1 and 2 in the output data as shown in the below example

Input Data

Header 1	Header 2	Header 3	Header 4	Header 5
.5SP07	954	VPS	NXP	NXP
.HKL5321	586	VTS	HKP	HKP
.5697P	86P	HKP	VPS	MSP
.55897	LEFT	NXP	VTS	VPS
.MXP01	RIGHT	MSP	MSP	VTS

Output Data

Header 1	Header 2	concatenate Headers	Header 3	Header 4	Header 5
.5SP07	954	VPS,NXP,NXP	VPS	NXP	NXP
.HKL5321	586	VTS,HKP,HKP	VTS	HKP	HKP
.5697P	86P	HKP,VPS,MSP	HKP	VPS	MSP
.55897	LEFT	NXP,VTS,VPS	NXP	VTS	VPS
.MXP01	RIGHT	MSP,MSP,VTS	MSP	MSP	VTS

Here is how you can do it.

Workflow:

1. Using record id to set row unique id

2. Using transpose tool select all the key data columns rest will be by default selected as value columns make sure dynamic or unknown column is checked this way any new columns will be selected in values.

3. Using summarize tool groupby record id and concat values.

4. Using find and replace to map the concat values to main data.

Hope this helps : )

Concat values.yxmd

All comments

One thing you can do is set the number of columns to really high number in text to column tool like below.

Then you can use data cleanse tool to remove null rows.

Hope this helps : )

split data.yxmd

Thanks helping, t have tried the workflow you have suggested but I'm not getting option option remove null data in my Alteryx designer version (2020.1.2.24185) it is because of the version

Hi atcodedog05

implemented the above solution and it work, as the data was coming from file hosted on web page so by default first column was link from where data is coming,

After using download tool, using delimiter \n we are able to split it into multiple rows and select the column which is required (which is second column)

Then again split the column using delimiter comma and using the title tool and cross tab able to get desired result.

Thanks for help much appreciated.

Happy to help : ) @KamalChoudhary

Cheers and have a nice day!

Hi @atcodedog05

I'm using find and replace to append columns in Table1, Not sure how to append dynamic columns

Problem = column are dynamic not fixed changed every time based on input ( using {Append fields to records} of find and replace tool)

Table1 Find column 2 i.e. ID

item	id
123	549
456	666
789	6614

Table2 and match in Replace field column 2 i.e. order-ID

Record	order-ID	1	2	3	4	5	6	7
1	549	xyz	abc
2	666	mhl	def
3	6614	ghi	jak	mno	qwe	tyu	ews	ghj
4	789	pqr	stu
5	899	fgh	klj

Expected Result only ids which matching with table 2 append columns

item	id	1	2	3	4	5	6	7
123	549	xyz	abc
456	666	mhl	def
789	6614	ghi	jak	mno	qwe	tyu	ews	ghj