Extract and exclude

[#1]

Hi,

I'm trying to select all of the rows with the value of 1 in the Violation column, extract the Device column value, then exclude all of the rows associate with those devices.  Anyone can help me coming up with a script?  Thanks!

[#2 · Accepted solution]

Below is a script which will perform this action

Names Default To Here(1);

dt = Current Data Table();
dt << Clear Row States;

violation_rows = dt << Get Rows Where(:Violation == 1);
violation_devices = Associative Array(dt[violation_rows, "Device"]) <<  get keys;

dt << Select Where(Contains(violation_devices, :Device)) << Exclude(1) << Clear Select;

1. Get row numbers where :Violation == 1
2. Use Data table subscripting and associative array to get the devices
3. Select rows with those devices and exclude them

You can easily also do this interactively (and this will be recorded by enhanced log):

1. Create a summary table with Device as Group and Violation as subgroup. Also make sure the tables are linked
2. Select 0 values from N(1) (indicates where Validation == 1) and then right click on rows to invert selection
3. Now you have the devices selected in your main table
4. You can now exclude these rows

JMP will create you a script like this

// Open Data Table: Sample(1).jmp
// → Data Table("Sample(1)")
Open("$DOWNLOADS/Sample(1).jmp");

// Data table summary
// → Data Table("Sample(1) By (Device)")
Data Table("Sample(1)") << Summary(Group(:Device), N, Subgroup(:Violation), Freq("None"), Weight("None"));

// Select matching cells
Data Table("Sample(1) By (Device)") << Select Where(:"N(1)"n == 0);


// Invert current selection
Data Table("Sample(1) By (Device)") << Invert Row Selection;


// Toggle selected rows' exclude state
Data Table("Sample(1) By (Device)") << Select Where(:"N(1)"n == 0) << Invert Row Selection << Exclude;


// Close Data Table: Sample(1) By (Device)
Close(Data Table("Sample(1) By (Device)"), NoSave);

and with some small modifications it will be fairly robust script be used in the future (use variables instead of table names, start it with Names Default To Here(1)... to mention some changes).

[#3]

Thank you for the prompt reply. You have just made my day!