Subscribe Bookmark RSS Feed

How can I find the row and column of a value within a data table?

natalie_

Community Trekker

Joined:

Jan 6, 2016

Hi all,

I need to find the column and row number of a particular value within a data table.   I know how to search for a value within a particular column (ie., row = dt1 << get rows where(:Device == "Device");) , which returns the row number, but what if I don't know what row it will be in?

Is it possible to do this in JMP?  I see how to do it from going to edit then search, but is there a way to do this in JSL?

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

If you're looking for text, then this example may be helpful:

dt = open("$sample_data\Bands Data.jmp");

char_cols = dt << get column names(Character, string);

search_string = "E310";

for (i = 1, i <= nitems(char_cols), i++,

    match_rows = dt << get rows where(as column(dt, char_cols[i]) == search_string);

    if (nrows(match_rows) > 0,

        print("Column: [" || char_cols[i] || "] Rows: " || char(match_rows));

    );

);

2 REPLIES
Solution

If you're looking for text, then this example may be helpful:

dt = open("$sample_data\Bands Data.jmp");

char_cols = dt << get column names(Character, string);

search_string = "E310";

for (i = 1, i <= nitems(char_cols), i++,

    match_rows = dt << get rows where(as column(dt, char_cols[i]) == search_string);

    if (nrows(match_rows) > 0,

        print("Column: [" || char_cols[i] || "] Rows: " || char(match_rows));

    );

);

natalie_

Community Trekker

Joined:

Jan 6, 2016

Thank you, your examples are always great!

I know that the value can only happen once in the file, so I made a while loop instead so that it would not continue to check the other columns for it.

file = Pick File();

dt = Open (file);

for(i=1, i<=ncols(dt), i++,

  column(dt,i) << data type(Character);

  column(dt,i) << set modeling type(continuous);

);

nCols = dt << Get Column Names ("Character");

found =0;

search_string = "Vtlin";

i=1;

While (found == 0,

  match_rows = dt << get rows where(as column(dt, nCols)==search_string);

  if(nrows(match_rows) > 0, found = 1, i++);

  if(i==nCols, found =1, );

);

vtlinCol = i;