- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Recode multiple columns with JSL script
I have a script with connects to an Access Database to retrieve some data for analysis. In this data there are several columns which have some null information in the data set. I know I can use the For Each Row command and recode a column to replace all the null data with the string "Blank". Is there a way to script this to work on all columns or do i need to use the For Each Row command on each column I wish to recode? I dont have a problem doing it that way, but it just seems there has to be a way to loop this for each column and shrink the code a bit.
Right now i have this:
For Each Row ( If( :Priority == "", :Priority = "Blank" ));
If possible i would simply like it to traverse through all the columns and anywhere there's no information to insert "Blank".
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Recode multiple columns with JSL script
You can have multiple statements within a for each row() clause. I.e. For Each Row( If( ... ); If( ... ), ... ).
Or perhaps better, loop over a list of columns (inside the For Each Row()-loop or stand-alone) as in the example below
dt = Current Data Table();
cols = dt << get column names;// or any list of character columns
For Each Row( For( i = 1, i <= N Items( cols ), i++, If( cols[i][] == "", cols[i][] = "Blank" ) ) );
// Alternatively...
For( i = 1, i <= N Items( cols ), i++, cols[i][dt << get rows where( cols[i][] == "" )] = "Blank");
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Recode multiple columns with JSL script
You can have multiple statements within a for each row() clause. I.e. For Each Row( If( ... ); If( ... ), ... ).
Or perhaps better, loop over a list of columns (inside the For Each Row()-loop or stand-alone) as in the example below
dt = Current Data Table();
cols = dt << get column names;// or any list of character columns
For Each Row( For( i = 1, i <= N Items( cols ), i++, If( cols[i][] == "", cols[i][] = "Blank" ) ) );
// Alternatively...
For( i = 1, i <= N Items( cols ), i++, cols[i][dt << get rows where( cols[i][] == "" )] = "Blank");
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Recode multiple columns with JSL script
That worked perfectly. Thank you so much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Recode multiple columns with JSL script
I have a similar request - how would I recode something with 40 columns with 3 options:
Questions 1 through 40 can have 3 possible outcomes: "1", "Completed", "0", "Incomplete", "NULL", "Pending"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Recode multiple columns with JSL script
Do you actually have to recode the values, or could you use Value Labels to just change the displaying of the values to the specified values? If you can use Value Labels, then you can do the following
1. Select All of the Columns you want to change
2. Go to the Cols pull down menu and select "Standardize Attributes..."
3. Go to "Standardize Properties" and select the Value Labels column property.
4. Complete the Labeling dialog box and then Click on "OK"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Recode multiple columns with JSL script
Thank you! That took care of it.