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
MuttonChops
Level III

JSL FOR loop to check values in cells and remove the 7th character if there is one

This snippet below is a portion of my script.  At this point I have a data table and some of the cells on various rows have an extra special character in the 7th position...yeah weird dataset.  Some don't.  There are multiple rows and multiple columns, all of which can vary in count.

 

So I'm using a FOR loop to check the value of each cell for each row and column then trim or remove the 8th character if it has it.  In fact, the script can simply do a left(dt,7) and I'd be happy with that.  Where I'm at though is stuck because when I use the GETVALUES function then it puts the cell values for that column in a matrix and I can't figure out how to do the left trim.  If I instead loop each row individually by removing the GETVALUES then I can trim the value properly but can't figure out how to force the new value into the cell.  I have included both ways in the script below.

 

 

 

//Loop to delete the closing crazy characters from each row, it is always the 7th character that needs to be removed.
		For(n = 1, n <= ncol(dtWAPFile), n++,
			colname = Column(n) << get name;
			For( k = 1, k <= N Rows( dtWAPFile ), k++,
				RowVal = dtWAPFile:colname << GetValues; //This gets a matrix
				NewRowVal = Left(RowVal,6); //This doesn't work with the matrix
				dtWAPFile:colname << setvalues(NewRowVal);//This doesn't work...
				);
		For(n = 1, n <= ncol(dtWAPFile), n++,
			colname = Column(n) << get name;
			For( k = 1, k <= N Rows( dtWAPFile ), k++,
				RowVal = dtWAPFile:colname[k]; //This gets the value for a row/column based on the loop
				NewRowVal = Left(RowVal,6); //This trims the value
				RowVal = setvalues(NewRowVal);//This doesn't work...can't seem to stuff it back in..
				);

 

1 ACCEPTED SOLUTION

Accepted Solutions
ian_jmp
Staff

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

Tweaking Jim's code a little:

 

NamesDefaultToHere(1);

// Test Table
dt = New Table( "Test",
					Add Rows( 4 ),
					New Column( "Column 1",
						Character,
						"Nominal",
						Set Values( {"123456", "1234567", "12345678", "1234"} )
					),
					New Column( "Column 2",
						Character,
						"Nominal",
						Set Values( {"123456789", "123", "123456", "1234567"} )
					)
				);

// Truncate each cell to the leftmost six characters
Wait(3);
cols = dt << getColumnNames("String");
for(c=1, c<=NItems(cols), c++,
	for(r=1, r<=NRow(dt), r++,
		Column(dt, cols[c])[r] = Left(Column(dt, cols[c])[r], 6);	
	);
);

View solution in original post

7 REPLIES 7
txnelson
Super User

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

I think you are making it too hard.  This ode should work

For( n = 1, n <= N Col( dtWAPFile ), n++,
	colname = Column( n ) << get name;
	For( k = 1, k <= N Rows( dtWAPFile ), k++,
		Column( dtWAPFile, n )[k] = Left( Column( dtWAPFile, n )[k], 6 )
	);
);
Jim
MuttonChops
Level III

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

Getting an error (below) with it but I like hearing that I'm making it too hard.  Hopefully we are close.

 

In the following script, error marked by /*###*/
For( n = 1, n <= N Col( dtWAPFile ), n++,
	colname = Column( n ) << get name;
	For( k = 1, k <= N Rows( dtWAPFile ), k++,
		Column( dtWAPFile, n )[k] = Left( Column( dtWAPFile, n )[/*###*/k], 6 )
	);
)
MuttonChops
Level III

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

If I step through the code and bypass the loop then it works.

txnelson
Super User

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

OK.....so I didn't test the code very well......or actually I really didn't test it.:-( 

Glad you got it figured out

Jim
MuttonChops
Level III

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

I didn't ever get it figured out but I found a work around.  For some reason I could step through the code and it would work but when I run the whole snippet it wouldn't....weird.

 

Ultimately I had to remove the first FOR loop and not embed the second FOR loop inside the first FOR loop. I'm hoping I had a simple mistake but I just couldn't get there and ran short on time.

 

ian_jmp
Staff

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

Tweaking Jim's code a little:

 

NamesDefaultToHere(1);

// Test Table
dt = New Table( "Test",
					Add Rows( 4 ),
					New Column( "Column 1",
						Character,
						"Nominal",
						Set Values( {"123456", "1234567", "12345678", "1234"} )
					),
					New Column( "Column 2",
						Character,
						"Nominal",
						Set Values( {"123456789", "123", "123456", "1234567"} )
					)
				);

// Truncate each cell to the leftmost six characters
Wait(3);
cols = dt << getColumnNames("String");
for(c=1, c<=NItems(cols), c++,
	for(r=1, r<=NRow(dt), r++,
		Column(dt, cols[c])[r] = Left(Column(dt, cols[c])[r], 6);	
	);
);
MuttonChops
Level III

Re: JSL FOR loop to check values in cells and remove the 7th character if there is one

Worked. Thanks guys! I love this forum btw, you guys are brilliant and make me look good at work :)