Choose Language Hide Translation Bar
Highlighted
buckmurdock
Level I

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ms
Super User ms
Super User

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

View solution in original post

5 REPLIES 5
Highlighted
ms
Super User ms
Super User

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

View solution in original post

Highlighted
buckmurdock
Level I

Re: Recode multiple columns with JSL script

That worked perfectly.  Thank you so much!

Highlighted
noemireyes
Level I

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"

Noemi Reyes, B.Sc., MSM
Highlighted
txnelson
Super User

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" 

Jim
Highlighted
noemireyes
Level I

Re: Recode multiple columns with JSL script

Thank you! That took care of it.

 

Noemi Reyes, B.Sc., MSM
Article Labels

    There are no labels assigned to this post.