cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
JensRiege
Level IV

Change Values in a Column to UpperCase

 

Question: I have a data table with data values in upper and lower case text strings in one column.

I expect that the code will need to loop through each row, select the column of interest and set the value in the column to uppercase. I am having an issue figuring out the correct syntax to do this. This is what I have so far, but don't think I am close enough. Thanks to any help that can be provided.

  For( k = 1, k <= N Rows( dt3 ), k++,

     Column( dt, :KEYWORD)[k] = UpperCase( Column( dt, :KEYWORD)[k]) );

 

1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User (Alumni)

Re: Change Values in a Column to UpperCase

So you are foring through the number rows in dt3 but affecting dt seems like an issue first off.  Plus the syntax is either :Keyword[k] or Column(dt, "Keyword")[k] or dt:Keyword[k].  

 

Here's a corrected one

Names Default to Here( 1 );
dt = new table("Test", 
	New Column("Keyword", character, <<Set Values({"abC", "Def", "ABC", "abcdfe"}))
);

For( k = 1, k <= N Rows( dt/*dt3*/ ), k++,
	//Column( dt, :KEYWORD)[k] = UpperCase( Column( dt, :KEYWORD)[k]) 
	Column(dt, "Keyword")[k] = UPPERCASE(Column(dt, "KEYWORD")[k]);
	
	//or
	//:Keyword[k] = Uppercase(:Keyword[k]);
);

But it'd likely be faster to just do all of them at once and set values. 

values = Column(dt, "Keyword") << Get Values();
u_values = parse(uppercase(char(values)));
Column(dt, "Keyword") << Set values(u_values);

 

Vince Faller - Predictum

View solution in original post

6 REPLIES 6
vince_faller
Super User (Alumni)

Re: Change Values in a Column to UpperCase

So you are foring through the number rows in dt3 but affecting dt seems like an issue first off.  Plus the syntax is either :Keyword[k] or Column(dt, "Keyword")[k] or dt:Keyword[k].  

 

Here's a corrected one

Names Default to Here( 1 );
dt = new table("Test", 
	New Column("Keyword", character, <<Set Values({"abC", "Def", "ABC", "abcdfe"}))
);

For( k = 1, k <= N Rows( dt/*dt3*/ ), k++,
	//Column( dt, :KEYWORD)[k] = UpperCase( Column( dt, :KEYWORD)[k]) 
	Column(dt, "Keyword")[k] = UPPERCASE(Column(dt, "KEYWORD")[k]);
	
	//or
	//:Keyword[k] = Uppercase(:Keyword[k]);
);

But it'd likely be faster to just do all of them at once and set values. 

values = Column(dt, "Keyword") << Get Values();
u_values = parse(uppercase(char(values)));
Column(dt, "Keyword") << Set values(u_values);

 

Vince Faller - Predictum
JensRiege
Level IV

Re: Change Values in a Column to UpperCase

Thank you for the quick solutions. It's nice to see two ways of doing this. Looks like I was close on the first one. Tested both and they work and will use the second one since it is faster.

Appreciate the help!

 

txnelson
Super User

Re: Change Values in a Column to UpperCase

The For Each Row() function will automatically loop through all rows in the data table, so the below is another way of handling your question

For Each Row( As Column( dt, "keyword" ) = Lowercase( As Column( dt, "keyword" ) ) );

Be a little cautious with the function, in that you can not have a For Each Row() function within another For Each Row() function

Jim
JensRiege
Level IV

Re: Change Values in a Column to UpperCase

Thank you for providing one more way to do this... It's useful when trying to understand the syntax. Since this one could have a conflict if another For Each Row() function is used, I will try the second solution provided above.
Thank you for providing an example with the 'For Each Row ()' function to do this.

Re: Change Values in a Column to UpperCase

Not sure if a script is necessary. Select the column with the character data, select Cols > Recode. Changing case is a built-in operation.

JensRiege
Level IV

Re: Change Values in a Column to UpperCase

Thank you Mark,
I am trying to create an automated process, which I why I was trying the script approach.
Appreciate the feedback,
Jens