topic Re: select rows of colnames based on value, then exclude those columns in another dt? in Discussions

select rows of colnames based on value, then exclude those columns in another dt?

aliegner1 — Sat, 10 Jun 2023 23:57:16 GMT

Looking for help....i'm wonding if a scripting syntax may have changed.

I'm running some statistics and t-tests on a data table with thousands of columns. I'm then generating a summary 'combined data table' to sort the p-value column. I then want to select/exclude based on p-value but it seems to not be working at all. any thoughts?

/***********************************/
/*pVal selection*/
// Create the combined data table, using xPath (return a list of the OutlineBoxes from the report that have the title Ordered Differences Report.  Then, use the first of those to make the combined data table.)
outlines = (Report( ow1[1] ) << Top Parent) << XPath( "//OutlineBox[text()='Ordered Differences Report']" );
dt1 = outlines[1][Table Box( 1 )] << Make Combined Data Table;

dt1 = current data table();

//Select the rows w/ low pVal, create a String value of all the Column Names, by selected row
pVals = Loc(dt1:"p-Value"n << get as matrix > 0.05); 
comparisonStrings = {};
For(i = 1, i <= N Items(pVals), i++,
	Insert Into(comparisonStrings, char(dt1:Y[pVals[i]]));
);
show(comparisonStrings);

//Go to source dt, select all Cols that match the string and exclude. You're left with only Cols from charts w low pVal.
dt_Data_Wide << clear select;
dt_Data_Wide << Clear Column Selection();
colList = dt_Data_Wide << Get Column Names( String ); /*gets a list of all columns*/
For( i = 1, i <= N Items( comparisonStrings ), i++,
  If( Contains( colList, Char( comparisonStrings[i] ) ),
  Column( dt_Data_Wide , Char( comparisonStrings[i] ) ) << Set Selected( 1 ) << exclude( 1 )
  )
);
dt_Data_Wide << clear select;
dt_Data_Wide << Clear Column Selection();

Re: select rows of colnames based on value, then exclude those columns in another dt?

txnelson — Wed, 23 Nov 2022 22:53:04 GMT

Your JSL seems to work in the simple example that I ran. It sets the column property to Excluded for those columns with a p-value greater than .05. What are you finding that is different?

Re: select rows of colnames based on value, then exclude those columns in another dt?

aliegner1 — Wed, 23 Nov 2022 23:17:08 GMT

It generates and sets the "ComparisonStrings" list

it clears selection of source table.

it generates the list of column names

but the looping to select and exclude seems to not do anything.

Re: select rows of colnames based on value, then exclude those columns in another dt?

txnelson — Thu, 24 Nov 2022 03:18:44 GMT

Your JSL is not doing anything that will change the data table you reference as "dt1"; If you want to eliminate the rows where the p-values are above .05, it would be a simple matter to add

dt1 << select rows(pVals);
dt1 << delete rows;

after your "Show( comparisonStrings );"