Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

"Embedded new line" message in cell containing numbers

irmb
7 - Meteor

Hello, i'm new to Alteryx. So i have a column that includes whole numbers as well as decimals. Here is a sample

2.5

40.5

3

23.5

2

1

 

The data type right now is a V_string but I will change in to a fixed decimal because we will need to perform a sum on the values later on.

 

For some of the numbers  that appear in the column, I have the message "This cell has an embedded new line". What does that mean and how do I eliminate the message.

 

Thanks

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @irmb 

 

This means you have the new line character (\n) present within your cells.

 

To get rid of them, you can use this RegEX.

 

REGEX_Replace([Field], "\n", "")

 

Cheers,

JoeS
Alteryx
Alteryx

As above, this means that there is a new line character - for example someone has pressed alt + enter in the cell in Excel to add a new line.

 

The above will work, also our Data Cleanse tool within the preparation section has the check box to remove embedded new lines as well.

danilang
19 - Altair
19 - Altair

Hi @irmb

 

Both @JoeS and @Thableaus are correct that the new line character(\n) is embedded in the cell.  However, if you simply remove it, you'll end up with the values concatenated in the same row, i.e  "2.540.5323.521".  

 

You'll to use either the Text to Columns tool or the Regex tool in tokenize mode to split the field on "\n" into separate rows

 

Dan 

 

 

 

 

 

 

 

 

irmb
7 - Meteor

Thanks everyone. So, this is the status...

 

I converted the column to "fixed" decimal. Then I used the data cleanse tool to replace nulls with blanks and zeros and to remove leading and trailing whitespace, tab lines, etc. and whitespace.

 

Then, i used the select tool to change the column from string to "fixed decimal" This is the error result now.

What do these messages mean? It says there are sample.JPGmany more digits after the decimal but that is not true--unless I can't see them. Thoughts on how to fix these issues?

 
danilang
19 - Altair
19 - Altair

Hi @irmb 

 

Notice the second message in your screen shot.  It has "23.524.5" in it.  This is the situation I referred to in my post.  When you simply remove the new lines, the number 23.5  was concatenated with the numbers after it giving an invalid number.  You need to split to rows using \n as the delimiter.  That will ensure that each of your values is on one row and can then be converted 

 

Dan 

irmb
7 - Meteor

Thanks, you are right. We actually uncovered a deeper issue.  The column should not include two values separated by a line in the first place. We will check with the data entry team to determine why this cell has two different values.

Labels