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

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

MuttonChops — Tue, 11 Apr 2017 01:45:42 GMT

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..
				);

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

txnelson — Tue, 11 Apr 2017 02:14:21 GMT

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

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

MuttonChops — Tue, 11 Apr 2017 02:29:27 GMT

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

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

MuttonChops — Tue, 11 Apr 2017 02:32:39 GMT

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

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

txnelson — Tue, 11 Apr 2017 03:02:06 GMT

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

Glad you got it figured out

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

MuttonChops — Tue, 18 Apr 2017 14:47:15 GMT

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.

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

ian_jmp — Tue, 18 Apr 2017 15:20:13 GMT

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

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

MuttonChops — Tue, 18 Apr 2017 15:46:46 GMT

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