topic Re: use of "for each row" with column name from a list in Discussions

use of "for each row" with column name from a list

dghidoni — Thu, 28 Mar 2013 13:33:00 GMT

Hi all

I am working on a way to clean a user-generated table and I'm incurring in an issue: the code compile correctly, but it doesn't work

I have a table with unknown columns (read: multiple, variable, hand-written by user) both in numbers and names.

I would like to perform a simple standardization, let's say polish the values from unwanted spaces and uppercase errors.

so I generate a list with column names, put up a cycle and place a for each row inside.

It compile, but doesn't work. any suggestion? the code is here

user_page = open ("\user\page.csv", labels(1));

full_list = user_page <<Get Column Names(string);

for (i=1,i<Nitems(full_list)+1,i++,

sclmn = ":"||full_list;

for each row (sclmn = trim (sclmn));

for each row (sclmn = lowercase (sclmn));

);

I tried several variations, using a column reference instead of a string, or using Eval(), but all failed to compile. This layout is the only one compiling, but still doesn't work. Can someone help me to understand why?

Re: use of "for each row" with column name from a list

ms — Thu, 28 Mar 2013 14:10:08 GMT

The trick is to make JMP recognize sclmn as a column. In the current script sclmn is just a string. Also, ForEachRow() often requires an empty index [ ] after column variables.

Here are two ways to do it:

From list of column names:

full_list = user_page << Get Column Names();

For( i = 1, i <= N Items( full_list ), i++,

sclmn = full_list[i];

For Each Row( sclmn[] = Trim( sclmn[] ) );

For Each Row( sclmn[] = Lowercase( sclmn[] ) );

);

Or from list of column names as strings:

full_list = user_page << Get Column Names( string );

For( i = 1, i <= N Items( full_list ), i++,

sclmn = Column( full_list[i] );

For Each Row( sclmn[] = Trim( sclmn[] ) );

For Each Row( sclmn[] = Lowercase( sclmn[] ) );

);

Re: use of "for each row" with column name from a list

dghidoni — Thu, 28 Mar 2013 14:38:18 GMT

thank you, the second approach worked. (I prefer that because I use the same list for something else)

I tried the column() way before posting, but it didn't worked without the []

I also looked through the debugger, and it was correctly traced as a column reference instead of a string, and still I don't understand why it needs the []. if you use a hardwired version, let's say

for each row (:update_01 = trim (:update_01));

it works without the [], and it seems to me that there is no references on that in the help or in the books ...

anyway, now it works. thank you!

Re: use of "for each row" with column name from a list

jeff3928 — Fri, 19 Apr 2013 17:23:28 GMT

According to the JMP Scripting Guide: "Always use a subscript on a column reference to access the values in the cells of columns." This seems to be the best practice although there are exceptions where it works without the square brackets as you point out.