Subscribe Bookmark RSS Feed

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

MuttonChops

Occasional Contributor

Joined:

Mar 30, 2017

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

Joined:

Jun 23, 2011

Solution

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);	
	);
);
7 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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

Occasional Contributor

Joined:

Mar 30, 2017

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

Occasional Contributor

Joined:

Mar 30, 2017

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

txnelson

Super User

Joined:

Jun 22, 2012

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

Occasional Contributor

Joined:

Mar 30, 2017

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

Joined:

Jun 23, 2011

Solution

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

Occasional Contributor

Joined:

Mar 30, 2017

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