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

Using a FOR loop in Formula to create a new column

jetpeach — Wed, 30 Mar 2016 20:37:08 GMT

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

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

txnelson — Wed, 30 Mar 2016 22:03:15 GMT

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

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

jetpeach — Wed, 30 Mar 2016 22:21:45 GMT

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.

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

ms — Wed, 30 Mar 2016 22:58:02 GMT

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

)

);

)

);

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