390582_en-US

liliconcom

How to select and exclude rows from dt1 based on unique list of values from dt2?

so I'm using dt1 to run t-test analysis (Mean by Chamber by ChartID) then creating a combined data table, dt2, of all the pvalues. I want to filter this down to rows where p<=0.05, then create a distinct, unique list of these low pValues and use that to replot my chart with just the low PValues.

 

I'm a bit stuck now trying to link back to the original dt1. The final select where doesn't seem to be working.

also to note, if there's a better way to select, exclude, join back to source dt, please let me know. I tried summary, associative array.

 

//creating the table of pValues
dt2 = Report( ow[1] )["Means Comparisons", "Comparisons for all pairs using Tukey-Kramer HSD","Ordered Differences Report"][Table Box( 1 )] << Make Combined Data Table;
dt2 << set name( "pValue PairWise by Channel Tukey")

//select and exclude high pVals
dt2 << select where("p-Value"n > 0.05);
dt2 << exclude;

//get list of remaining Unique Chart_ID's
summarize(uniqueVals=by(CHART_ID));

//trying to select on the original source data
dt1 << select where( contains( uniqueVals, :CHART_ID));
//then need to invert and exclude

AnalyticsScriptingRe: How to select and exclude rows from dt1 based on unique list of values from dt2?

Are your chart IDs, by chance, numeric? The by() result in Summarize is a list of character strings--even if the original group identifiers were numbers. This will prevent those list elements (which, again, are strings) from matching any values in the original table (which are numbers).

 

??

 

Cheers,

Brady

 

Re: How to select and exclude rows from dt1 based on unique list of values from dt2?

My first guess would be mismatch between character and numerical values.

 

Below is an example with Big Class:

Names Default To Here(1);
dt1 = Open("$SAMPLE_DATA/Big Class.jmp");
ow = Oneway(Y(:height), X(:age), All Pairs(1));
dt2 = Report(ow)["Means Comparisons", "Comparisons for all pairs using Tukey-Kramer HSD","Ordered Differences Report"][Table Box( 1 )] << Make Combined Data Table;
dt2 << set name( "pValue PairWise by Channel Tukey");
dt2 << select where(:"p-Value"n > 0.05);
dt2 << exclude;
summarize(dt2, uniqueVals=by(:level)); //check out the type of :level column, could be character
//convert to numbers OR you could convert values in dt1 to characters
For( i = 1, i <= N Items(uniqueVals ), i++,
 uniqueVals[i] = Num(uniqueVals[i] )
);

dt1 << select where(Contains(uniqueVals, :age));

Re: How to select and exclude rows from dt1 based on unique list of values from dt2?

@brady_brady 

good questions. So my source data (dt1) has Chart_ID as Numeric Nominal. After creating charts and running t-Test then outputting a Combined Data Table (dt2) the Chart_ID column is Character Nominal.