BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
JensRiege
Community Trekker

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]) );

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User

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
6 REPLIES 6
vince_faller
Super User

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
Community Trekker

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!

 

0 Kudos
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
Community Trekker

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.
0 Kudos
Highlighted

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.

Learn it once, use it forever!
JensRiege
Community Trekker

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
0 Kudos