split into rows

[BOBBY321]

Hi everyone,

I came across following problem,:

The source excel data is like :

data	org	company	personal	usage	info
2018-03-15 2018-03-15	398783614 398783614	31050161393600002149 31050161393600002149	6236684220008412455 6217000130041118372	salary salary	2331.78 SH31000009193009701 2215.95 SH31000009193009701
2018-05-15 2018-05-15	398783614 398783614	31050161393600002149 31050161393600002149	0000100532908 6217003860022562324	salary salary	519.0091930 3591.18 SH31000009193009701
2018-03-23 2018-03-23	398783614 398783614	31050161393600002149 31050161393600002149	6217001820025722459 6236680130004083750	salary salary	1401.65 SH31000009193009701 1503.5 SH3100009193009701

every cell contains two rows of data and however combined as one with a line break  '\n'

And i would like to split the data like this :

data	org	company	personal	usage	info
2018-03-15	398783614	31050161393600002149	6236684220008412455	salary	2331.78 SH31000009193009701
2018-03-15	398783614	31050161393600002149	6217000130041118372	salary	2215.95 SH31000009193009701
2018-05-15	398783614	31050161393600002149	0000100532908	salary	519.009193
2018-05-15	398783614	31050161393600002149	6217003860022562324	salary	3591.18 SH31000009193009701
2018-03-23	398783614	31050161393600002149	6217001820025722459	salary	1401.65 SH31000009193009701
2018-03-23	398783614	31050161393600002149	6236680130004083750	salary	1503.5 SH3100009193009701

it seems the text-to-column does not help.

How  Alteryx achieve this? Need help.

Thanks

[messi007]

@BOBBY321,

Could you share the source file (Excel or CSV) it will help us to give you the solution.

Regards,

[Luke_C · Accepted solution]

Hi @BOBBY321 

The text to columns is the right tool, but since it only does one field I'd suggest transposing your data to do it in one shot then crosstabbing back:

[BOBBY321]

I have upload a sample. It is from an OCR result, so have much to do with the data cleaning 

[binuacs]

@BOBBY321 one way of doing this

[BOBBY321]

That helps. Thank you Luke

[IraWatt]

Hey @BOBBY321,

Here is another way of doing this:

This solution uses the transpose and cross tab tools which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117

Any questions or issues please ask :)
HTH!
Ira

[Emmanuel_G]

Hi @BOBBY321,

Find a solutio attached !

[BOBBY321]

Another question :

Does anyone know the way to filter all rows in one column which contains a  line break  '\n'  

[Luke_C]

Hi @BOBBY321 

Something like this should work:

Contains([data],"
")