Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Using a FOR loop in Formula to create a new column

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 30, 2016 1:37 PM
(11065 views)

hi folks, I have a question regarding how I can combine a number of columns created in my table, into a new column that describes whether any of these columns have a specific char value ("screened").

Currently my datasets are analyzed with a script that adds "n" new columns that contain the char values "screened" and "unscreened" based on formulas in the script.

My goal is to include in the script a final step that adds a new column that evaluates the value in each of the 'n' new columns and if for each row, any of those columns are 'screened' then it labels that row as screened.

If the number of new columns was always the same, I would just write a long-ish If statement, checking the value in each column. However, I need to script it to look at 'n' number of columns. I thought to use a FOR loop inside of a forumula, e.g., something like:

dt << New Column("All_screens",character,nominal,setformula(

If( For( i = 1, i <= N Items( individualScreenColumns ), individualScreenColumns* == "Screened"), ---> need some magic such that if any of the screening columns contain screen, then ---> "Screened", "Unscreened" ));*

But since FOR loops don't return any value, I couldn't figure this magic out. Does anyone have any thoughts?

Thanks,

--Peach

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

The implementation of row-wise operations across an unknown number of columns in column formulas is not very straightforward (however magic is not required).

Below are some ideas taking advantage of local variables:

// Just an example table

n = **5**;

nr = **10**;however

dt = New Table**(**"Screening summary", Add Rows**(**nr**)**, Add Multiple Columns**(**"X", n, character**))**;

For**(**i = **1**, i <= n, i++,

Column**(**dt, i**)** << **Set Each Value****({**"screened", "unscreened"**}[****1** + **(**Random Integer**(****1**, nr**)** > **1****)])**

**)**;

// New column indicating if any column contains "screened"

dt << **New Column****(**"Screened",

Character,

Formula**(**

Local**({**i = **0**, n = N Col**(**Current Data Table**())** - **1****}**,

If**(**Contains**(**Repeat**({**i++ ; Column**(**i**)[]}**, n**)**, "screened"**)**,

"screened"

**)**

**)**

**)**

**)**;

// The same but with a For() loop; less neat code but probably more efficient than Repeat() for large n

// because no intermediate lists are built

dt << **New Column****(**"Screened 2",

Character,

Formula**(**

Local**({**i, n = N Col**(**Current Data Table**())** - **2**, s = ""**}**,

For**(**i = **1**, i <= n, i++,

If**(**Column**(**i**)[]** == "screened",

s = "screened";

Break**()**;

**)**

**)**;

s;

**)**

**)**

**)**;

// Alternatively, return nr of "screened"

dt << **New Column****(**" Nr of Screened",

Numeric,

Formula**(**Local**({**i = **0**, n = N Col**(**Current Data Table**())** - **3****}**, Sum**(**Repeat**({**i++ ; Column**(**i**)[]** == "screened"**}**, n**))))****)**;

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Using a FOR loop in Formula to create a new column

I use a boulian summation to solve the issue you are talking about. If you have 4 columns, a,b,c,d, I have used a formula of:

If(sum(a=="screened",b=="screened",c=="screened",d=="screened")>0, "screened","")

Jim

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Using a FOR loop in Formula to create a new column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

The implementation of row-wise operations across an unknown number of columns in column formulas is not very straightforward (however magic is not required).

Below are some ideas taking advantage of local variables:

// Just an example table

n = **5**;

nr = **10**;however

dt = New Table**(**"Screening summary", Add Rows**(**nr**)**, Add Multiple Columns**(**"X", n, character**))**;

For**(**i = **1**, i <= n, i++,

Column**(**dt, i**)** << **Set Each Value****({**"screened", "unscreened"**}[****1** + **(**Random Integer**(****1**, nr**)** > **1****)])**

**)**;

// New column indicating if any column contains "screened"

dt << **New Column****(**"Screened",

Character,

Formula**(**

Local**({**i = **0**, n = N Col**(**Current Data Table**())** - **1****}**,

If**(**Contains**(**Repeat**({**i++ ; Column**(**i**)[]}**, n**)**, "screened"**)**,

"screened"

**)**

**)**

**)**

**)**;

// The same but with a For() loop; less neat code but probably more efficient than Repeat() for large n

// because no intermediate lists are built

dt << **New Column****(**"Screened 2",

Character,

Formula**(**

Local**({**i, n = N Col**(**Current Data Table**())** - **2**, s = ""**}**,

For**(**i = **1**, i <= n, i++,

If**(**Column**(**i**)[]** == "screened",

s = "screened";

Break**()**;

**)**

**)**;

s;

**)**

**)**

**)**;

// Alternatively, return nr of "screened"

dt << **New Column****(**" Nr of Screened",

Numeric,

Formula**(**Local**({**i = **0**, n = N Col**(**Current Data Table**())** - **3****}**, Sum**(**Repeat**({**i++ ; Column**(**i**)[]** == "screened"**}**, n**))))****)**;