Working with excluded rows?

[#1]

Hi everyone,

I have two columns.  In the first column (Site) it either has the number 1 or 2 beside it.  In the second column, there are some numerical values. 

I have created a function which calculates rank and percentile.  First, the function calculates in a new column the rank and percentile.  Then the function selects all the rows that have the value 1 in site 1 and counts them.  Then it selects and excludes all the rows with the value 2 in the site column and then calculates the rank and percentile in another column.  All of this is working.

The problem comes in when I want to calculate the rank and percentile after selecting and excluding rows with the value 2 in the Site column.  It doesn't return any errors, but there are no values in the column.  I am assuming this has to do with the exclude() expression in my formula.  If I remove the if statement from F3 so that it is exactly like F, I get the same values as the column beside it, but if I keep F3 the way it is, no values! 

What am I missing?  Sorry if this is very obvious...

rpFunction = Function( {row, test},

      //row -->  how many rows(devices) are in the data table?

      //test --?  which test are we calculating RP for?

      Current Data Table( dtSummary );

      col = Column( test );

      newCol = "RP " || test;

      F = Eval Expr(  100*(Col Rank( col )/Expr(row) ));

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << Select Where(:Site==1);

      row1 = nrows(dtSummary<<get selected rows());

      dtSummary << Select Where(:Site==2);

      row2 = nrows(dtSummary<<get selected rows());

      dtSummary << Select Where(:Site == 2);

      dtSummary << exclude;

      newCol = "RP " || test || " Site 1";     

      F1 = Eval Expr(  100*(Col Rank( if(excluded(),.,col) )/Expr(row1) ));

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F1 ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << select where(:Site==2);

      dtSummary << unexclude;

      dtSummary << Select Where(:Site == 1);

      dtSummary << exclude;

      newCol = "RP " || test || " Site 2";     

      F2 = Eval Expr(  100*(Col Rank( if(excluded(),.,col) )/Expr(row2) ));

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F2 ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << select where(:Site==1);

      dtSummary << unexclude;

);

[#2]

EDIT:  F3 in my explanation should really say F2.

[#3]

not sure this is helpful but in older versions of the program column formulas were not sensitive to exclusions.

otherwise, it is better to do the calculations based on <<  get rows where () rather than on selection.

ron

[#4]

Natalie,

I took your code, and made a very slight change to it; I changed the unexclude and exclude to exclude(0|1).  This insures the correct action of the message.  The exclude and unexclude are actually switches, not absolute messages.

The code seems to work:

rpFunction = Function( {row, test},

      //row -->  how many rows(devices) are in the data table?

      //test --?  which test are we calculating RP for?

      Current Data Table( dtSummary );

      col = Column( test );

      newCol = "RP " || test;

      F = Eval Expr(  100*(Col Rank( col )/Expr(row) ));

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << Select Where(:Site==1);

      row1 = nrows(dtSummary<<get selected rows());

      dtSummary << Select Where(:Site==2);

      row2 = nrows(dtSummary<<get selected rows());

      dtSummary << Select Where(:Site == 2);

      dtSummary << exclude;

      newCol = "RP " || test || " Site 1";

      F1 = Eval Expr(  100*(Col Rank( if(excluded(),.,col) )/Expr(row1) ));

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F1 ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << select where(:Site==2);

      dtSummary << exclude(0);

      dtSummary << Select Where(:Site == 1);

      dtSummary << exclude(1);

      newCol = "RP " || test || " Site 2";

      F2 = Eval Expr(  100*(Col Rank( if(excluded(),.,col) )/Expr(row2) ));

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F2 ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << select where(:Site==1);

      dtSummary << exclude(0);

);

[#5]

Hi Jim,

Thanks for getting back to me.  Unfortunately, the code still is not working

I noticed that in the code you sent me that you didn't add an exclude(1) under the Site 2 selection.  I added it, but unfortunately it did not work.  I attached a screen show to show you what my data table looks like.  The first two rp columns are what I want, but the last one doesn't have any values.

rpFunction = Function( {row, test},

        //row -->  how many rows(devices) are in the data table?

      //test --?  which test are we calculating RP for?

      Current Data Table( dtSummary );

      col = Column( test );

      newCol = "RP " || test;

      F = Eval Expr(  100*(Col Rank( col )/Expr(row) ));

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << Select Where(:Site==1);

      row1 = nrows(dtSummary<<get selected rows());

      dtSummary << Select Where(:Site==2);

      row2 = nrows(dtSummary<<get selected rows());

      dtSummary << Select Where(:Site == 2);

      dtSummary << exclude(1);

      newCol = "RP " || test || " Site 1";

      F1 = Eval Expr(  100*(Col Rank( if(excluded(),.,col) ))/Expr(row1) );

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F1 ) ) );

      col = Column(newCol);

      col << delete formula;

      dtSummary << select where(:Site==2);

      dtSummary << exclude(0);

      dtSummary << Select Where(:Site == 1);

      dtSummary << exclude(1);

      newCol = "RP " || test || " Site 2";

      F2 = Eval Expr(  100*(Col Rank( if(excluded(),.,col) ))/Expr(row2) );

      dtSummary << New Column( newCol, Numeric, Formula( Name Expr( F2 ) ) );

      col = Column(newCol);

      col << delete formula;

        dtSummary << select where(:Site==1);

      dtSummary << exclude(1);

);

[#6]

hi natalie_​

perhaps you could use a wait() between selections.

otherwise, you will save time using subsets of the original table and updating the original table with the calculations you need. just make sure you do not override data in the original table.

ron

[#7 · Accepted solution]

Natalie,

I think I found it.

The second time you are assigning "Col=Newcol", I actually don't think you want to be assigning it to the new column.  You actually want to keep col pointing to the Rp Irgl column.  Not to the new column called Rp Irgl for Site 1.  You use the value of col in your formula, and you have just reassigned it to the Rp Irgl for Site 1.

I just changed the

col=newcol;

col<<delete formula;

to

xcol=newcol;

xcol<<delete formula;

The third use of col=newcol; and col<<delete formula; doesn't have to change, since you are not creating any new columns with the value of col in the formula.

[#8]

Thank you very much Jim.  It is working!  I didn't even think to look at that.  I was so fixated on it being the exclusion of rows since it was new to me and didn't look at the obvious!  Thanks again.

[#9]

Wouldn't it be more straightforward to target Site directly in the If-statement and avoid excluding rows altogether? For example:

rpFunction = Function({test},

    F = Eval Expr(100 * (Col Rank(Expr(Column(test))) / Col Number(:Site)));

    dtSummary << New Column("RP " || test, Numeric, Formula(Name Expr(F)));

    F = Eval Expr(100 * (Col Rank(If(:Site != 1, ., Expr(Column(test)))) / Col Sum(:Site == 1)));

    dtSummary << New Column("RP " || test || " Site 1", Numeric, Formula(Name Expr(F)));

    F = Eval Expr(100 * (Col Rank(If(:Site != 2, ., Expr(Column(test)))) / Col Sum(:Site == 2)));

    dtSummary << New Column("RP " || test || " Site 2", Numeric, Formula(Name Expr(F)));

);

[#10]

Hi MS,

Thanks for getting back to me.  I realized this after while I was searching through all the exclusion stuff.  Initially I thought I had to exclude rows so that's why I went with it.  I like the simplicity of your code way more, thanks!