Subscribe Bookmark RSS Feed

Recode multiple columns with JSL script

buckmurdock

Community Trekker

Joined:

Dec 17, 2013

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".

1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

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

5 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

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

buckmurdock

Community Trekker

Joined:

Dec 17, 2013

That worked perfectly.  Thank you so much!

noemireyes

Community Trekker

Joined:

Mar 23, 2017

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"

Noemi Reyes, B.Sc., MSM
txnelson

Super User

Joined:

Jun 22, 2012

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" 

Jim
noemireyes

Community Trekker

Joined:

Mar 23, 2017

Thank you! That took care of it.

 

Noemi Reyes, B.Sc., MSM