- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.. );
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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);
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 )
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 ) ); )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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);
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content