Subscribe Bookmark RSS Feed

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

dghidoni

Community Trekker

Joined:

Jan 23, 2012

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

Joined:

Jun 23, 2011

Solution

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

);

3 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

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

Community Trekker

Joined:

Jan 23, 2012

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

Community Trekker

Joined:

Jun 29, 2012

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.