cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
jetpeach
Level II

Using a FOR loop in Formula to create a new column

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
ms
Super User (Alumni) ms
Super User (Alumni)

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

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

View solution in original post

3 REPLIES 3
txnelson
Super User

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
jetpeach
Level II

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

thanks Jim. since i don't know the number of columns, I'll try to put your equation into an expr() using a for loop, and then move this to the if statement with an eval( ) of the expression generated in the for loop.

ms
Super User (Alumni) ms
Super User (Alumni)

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

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