Hi I have a problem separating data stuck in one column

[KamalChoudhary]

Hi,

I'm  trying to separate data stuck in once cell of file hosted on webpage, we are able to get it via download tool 

1- using delimiter  \n  we are able to split it into multiple rows 

1,2,abc,dfg

4,xyz,xdf,fdgd,fhdhf

5,ght,asdf,qwe

2 - now we need to split the data into columns using delimiter comma ","but each row data is not fixed we are not sure how many column will be there so we can split it to columns

because using text to column we need to define how columns 

1	2	abc	dfg
4	xyz	xdf	fdgd	fhdhf
5	ght	asdf	qwe

Im using text to column tool , relevant tools I could use with this one. Can anyone help me with this one pls?

[atcodedog05]

Hi @KamalChoudhary 

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 : )

[KamalChoudhary]

Hi @atcodedog05

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 

[atcodedog05 · Accepted solution]

Hi @KamalChoudhary 

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 : )

[KamalChoudhary]

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.

[atcodedog05]

Happy to help : ) @KamalChoudhary 

Cheers and have a nice day!

[KamalChoudhary · Accepted solution]

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

[atcodedog05 · Accepted solution]

Hi @KamalChoudhary 

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 : )

[KamalChoudhary]

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

[atcodedog05]

Hi @KamalChoudhary 

Sadly find and replace append option cannot accommodate dynamic columns. As suggested in the other post you might have to use join tool.

Hope this helps : )