topic Re: Change Values in a Column to UpperCase in Discussions

Change Values in a Column to UpperCase

JensRiege — Thu, 06 Jun 2019 23:38:21 GMT

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

Re: Change Values in a Column to UpperCase

vince_faller — Thu, 06 Jun 2019 23:56:39 GMT

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

Re: Change Values in a Column to UpperCase

txnelson — Fri, 07 Jun 2019 00:16:11 GMT

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

Re: Change Values in a Column to UpperCase

Mark_Bailey — Fri, 07 Jun 2019 13:19:23 GMT

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

Re: Change Values in a Column to UpperCase

JensRiege — Fri, 07 Jun 2019 16:50:41 GMT

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

Re: Change Values in a Column to UpperCase

JensRiege — Fri, 07 Jun 2019 17:06:27 GMT

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!

Re: Change Values in a Column to UpperCase

JensRiege — Fri, 07 Jun 2019 17:11:17 GMT

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.