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.
Choose Language Hide Translation Bar
dghidoni
Level II

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

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

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

);

View solution in original post

3 REPLIES 3
ms
Super User (Alumni) ms
Super User (Alumni)

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

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

);

dghidoni
Level II

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

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!

jeff3928
Level III

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

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.