Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
SamKing
Level III

How to find row and column (cell) that matches a value

I have tables that are generated and the format is different from time to time and it breaks my script.  The tables are massive and so the way i handle my loops is that i import the first 100 rows and 5 columns, for speed.  Then i search a particular column for a keyword to help me know where which row the real data starts.  I close the file then reopen with that variable identifying the column header and data start.

 

However, as mentioned above about 1% of the files are dorked up and have extra columns at the beginning and sometimes a few hundred random rows so it trips up my script.  I was thinking that if I can search the whole abreviated table for my keyword then i can get around it but i'm unsure how to search multiple columns at once, especially when the column names change.

 

Here is the method that I currently use to identify my row, looking in a particular column.  How do i open this up to search columns 1-10 or even the whole table?

//nr1 becomes the column headers and nr2 becomes the first row of data when i reopen the full table.
nr1 = dt << get rows where( :Col1 == "customparameter" ); nr2 = nr1 + 1;
2 REPLIES 2
Highlighted
txnelson
Super User

Re: How to find row and column (cell) that matches a value

You can refer to columns in a data table by number

dt << select where( column(dt, 3 ) == "Top" );

or you can easily retrieve the column names in a data table, and refer to those names

colNames = dt << get column names;
dt << select where( column( dt, colNames[3] == "Top" );

so once you have extracted you sample, you can find the names of the columns or just search by column number.

Jim
Highlighted
SamKing
Level III

Re: How to find row and column (cell) that matches a value

@txnelson, thanks for the response.  The column names are not indicative of the content, they are really screwy tables.  That is why i have to locate where the actual data starts, close the table then re-import the full table with the correct row identifying the columns.

 

Your comment though made me realize that i can grab all the column names then do a loop through each column and if it finds the matching cell value then assign the row value to my variable and exit the loop.