merging values from 2 columns into one (mixed numeric and character values)

[#1]

Hi

I have 2 columns, one containing numeric values, and one containing strings.

I want to create a third column that takes the numeric value if the string value is 'parametric', and the string value if it's not.

I tried this, but I'm getting an error "Column TEST_RESULT requires numeric values", although it's defined as Character type column.

I'm using JMP15 if it matters.

col_test_result =dt <<new column ("TEST_RESULT",Character);

Column(dt_subset,"TEST_RESULT")<<set formula(if(:COMMENTS=="Parametric",:NUMERIC_RESULT,:COMMENTS));

Later on I want to split this column so the output table will have either numeric or character type data, will JMP be able to set it to numeric based
on the contents although the source column was character?

[#2]

You need to use the Char() function to convert the numeric data to character

col_test_result = dt << New Column( "TEST_RESULT", Character );

dt_subset:TEST_RESULT << set formula( If( :COMMENTS == "Parametric", Char( :NUMERIC_RESULT ), :COMMENTS ) );

[#3]

You cannot have mixed values in one column. You could add Char() around your :NUMERIC_RESULT if those are numeric values

Column(dt_subset, "TEST_RESULT") << set formula(If(:COMMENTS == "Parametric", Char(:NUMERIC_RESULT), :COMMENTS));

And when you split the column just set the data type to what you want. Do note that if you have character values in numeric column, those will be changed to missing.

[#4]

Thanks ! adding the 'char()' solved the first issue.

But now - I have 2 other issues, after splitting the merged column.
I'm splitting it by another column, so the resulting columns all have one type of data (string or numeric), there are ~150 different columns.

The problems :

1. All the split columns are still with the original formula from merged column. How do I make the source column a value (like in excel - paste speical, values).

2. All the split columns are generated as character type, and that's not what I wanted, I want them to got automatic type based on their content, just like
when I'm opening a csv file - JMP sets the type automatically, so if all values in a column are numeric, it will get numeric type.

[#5]

Here is a little piece of code that will go through a data table and convert any character column that is made up of all valid numeric values, to a numeric data type;

Names Default To Here( 1 );
dt = Current Data Table();

For Each( {value, index}, dt << get column names( string ),
	If( Column( value ) << get data type == "Character",
		vals = Column( value ) << get values;
		nonMissing = Col Number( As Column( value ) );
		Column( value ) << set data type( numeric ) << modeling type( continuous );
		If( nonMissing != Col Number( As Column( value ) ),
			Column( value ) << set data type( character ) << set values( vals )
		);
	)
);

[#6]

Thanks ! that looks exactly what I need, but unfortunately it's not working.

I tried to debug - the first line is working - getting list of columns names.

The second one get the below error. any idea?

[#7]

Just as a comment:

It is not a restriction, but it is the basic mindset of JMP that each column has a defined datatype, that you can actively set according to your needs.

The datatype, once set properly, makes following analyses so easy to perform.

[#8]

I think you're using < Version 16, so you have to use std "For" syntax, see

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

// JMP16 "For Each"
dt << get column names( string );
For Each( {value, index}, dt << get column names( string ), Print( value ) );

// JMP <16 "For"
col_lst = dt << get column names( string );
For( i = 1, i <= N Items( col_lst ), i++,
	Print( col_lst[i] )
);

[#9]

Thanks it's working now !

now just one thing - how do I get rid of the original formula? I just want the resulting value of the formula, is it possible?

[#10]

well sort of working.. I found a problem. There was a column with 'char' data type that had a fixed string content : "R001"

after running the script it changes to numeric type, with value of '1'. Why is this hapenning?