480806_en-US

liliconcom

looping issue when column as variable

I have a table call dt and it has many columns. I want to find any missing in specific columns and fill with "N". Below is my script, but the col[i] variable not work.  I tried replace as column(dt, col[i]) with column(dt, col[i]), but not working. However, when I replace the as column(dt, col[i]) with the true column name (example, :A), the script works. Any suggestion where is my mistake?

col={"A", "B", "C", "D", "E"};

For( i = 1, i<= N Items( col ), i++,
 empty_rows = dt << get rows where (is missing(as column(dt, col[i])));
 if (nrows(empty_rows) > 0,
  as column(dt, col[i])[empty_rows] = "N";
 );
);

ScriptingRe: looping issue when column as variable

Your code works fine.  Given a data table

and running a copy of your code

names default to here(1);
dt=current data table();

col={"A", "B"};

For( i = 1, i<= N Items( col ), i++,
 empty_rows = dt << get rows where (is missing(as column(dt, col[i])));
 if (nrows(empty_rows) > 0,
  as column(dt, col[i])[empty_rows] = "N";
 );
);

It results in the following

The issue you may be having is if your A,B,C,D,E columns are numeric, you will not be able to place a character value into the cell.  JMP will convert it to a missing value "."

Re: looping issue when column as variable

Hi Jim,

Thanks Jim for your help.

I tried many times and it is still not working. after your validation on the code. I decided to close JMP and reopen it. This time, it is working. I wonder if it is cause by the cache issue? Is there a way to clean jmp cache without restarting JMP? My assumption might be wrong, but anyone can give suggestion will be helpful.

Re: looping issue when column as variable

Without seeing your whole code, the issue could be with dt variable. In general it is a good idea to start JSL scripts with Names Default To Here(1);. This will allow unresolved names to be stored in Here namespace.

You can clear variables with Delete Symbols(), Delete Globals() and sometimes you might have to clear some namespaces also.

 

Here is also other option for changing empty values using Loc and data table subscripting:

Names Default To Here(1);
dt = Current Data Table();

col = {"A", "B"};

For(i = 1, i <= N Items(col), i++,
 empty_rows = Loc(dt[0, col[i]], "");
 If(N Rows(empty_rows) > 0,
  dt[empty_rows, col[i]] = "N";
 );
);