topic Re: In what way would it be possible to apply the same data filter to multiple tables dynamically? in Discussions

In what way would it be possible to apply the same data filter to multiple tables dynamically?

gv — Mon, 28 Jul 2014 15:31:35 GMT

Hi all,

I am currently building a data analysis tool using JMP. Basically we will use JMP to allow our client to interact with simulation output that was generated using other software.

The simulation output is stored in multiple tables with varying characteristics but the same identifiers. For example, one table holds output values globally associated with 1 simulation replication, a different table holds output associated with each agent present in a simulation replication (and thus has multiple lines per replication), and another table might hold output for each time step of each simulation replication (thus including many lines per replication). What all tables have in common are columns with identifiers and input parameters.

Now the challenge: I would like to be able to create a data filter that filters all data tables simultaneous, (of course this is done on the basis of the information they share (for example an identifier)). My underlying goal is to organize the data in the most convenient table format while still being able to dynamically present information from different tables using graphs etc.

Any thoughts you might have, please let me know!

Thank you in advance

Re: In what way would it be possible to apply the same data filter to multiple tables dynamically?

pmroz — Tue, 29 Jul 2014 14:15:28 GMT

Don't know if you could filter things simultaneously, but could add flag columns to each table, where a 1 means that this row meets some criteria, and a 0 means it doesn't.

Re: In what way would it be possible to apply the same data filter to multiple tables dynamically?

gv — Fri, 01 Aug 2014 09:18:53 GMT

Thank you for your reply PMroz.

I have been experimenting with something similar to this example: http://support.sas.com/demosdownloads/downarea_t4.jsp?productID=109646.

I have created two tables of which the include/exclude state of table 2 is depended on that of table 1 (in the future this could be expanded to also include the other states such as selected, visible, colour etc.).

Unfortunately the result is not robust yet. It works sometimes, but after filtering (include/exclude) a couple of rows or doing something else it stops working. I suspect it could be related to which data table is current but I am not sure. Using the script I wrote from the script window (and not embedded in table1) is slightly more robust but still sometimes fails to do anything.

Any thoughts on how to further improve this approach are greatly appreciated!

Just run the script for the two tables in the attachment to have a look for yourself. The script I use for actually connecting the tables is also at the bottom of the attachment.

Brief summary:

Both table 1 and table 2 have a column RepID.

If a row in Table 1 is included/excluded the column :Excluded does this (for some reason the dt/dt2 reference is lost after launcing the tables so I define it here again):

Formula(

If( Excluded( Row State( Row() ) ),

dt = Data Table( "linktable1.jmp" );

dt2 = Data Table( "linktable2.jmp" );

Current Data Table( dt );

dt = Data Table( "linktable1.jmp" );

dt2 = Data Table( "linktable2.jmp" );

Current Data Table( dt );

)

Then column :parser does this:

If(:Excluded == 1, Eval(Parse(:ScriptEx)), :Excluded == 0, Eval(Parse(:ScriptInc)))

Which refers to this script (in :ScriptEx):

RepIDex = dt:RepID[];

Excludelist = List();

Excludelist = As List( dt2 << Get rows Where( dt2:RepID == RepIDex ) );

Current Data Table( dt2 );

Eval( For( i = 1, i <= N Items( Excludelist ), i++, Row State( Excludelist ) = Excluded State( 1 ) ) );

See attachment for the whole script inc. tables.

Re: In what way would it be possible to apply the same data filter to multiple tables dynamically?

gv — Wed, 29 Oct 2014 14:28:39 GMT

I have found a much more efficient way to deal with the problem then my original approach.

To transfer the row state (included and selected) of dtglobal to dt1, dt2, dt3 you can use the following, it works fast and dynamic even for large datatables. All you need is a column with a corresponding 'key' (numeric value) in each table.

//Transfers row state of dtglobal to other tables

statehandler = dtglobal << Make Row State Handler( statetransfer );
statetransfer = Function( {Y},
dt1 << clear row states;
dt2 << clear row states;
dt3 << clear row states;
excluderows = dtglobal << get excluded rows;
excludekeys = dtglobal:Key << get values;
excludekeys = excludekeys[excluderows];
dt1 << Select Where( Contains( Matrix( excludekeys ), :Key ) );
dt1 << exclude;
dt2 << Select Where( Contains( Matrix( excludekeys ), :Key ) );
dt2 << exclude;
dt3 << Select Where( Contains( Matrix( excludekeys ), :Key ) );
dt3 << exclude;

selectrows = dtglobal << get selected rows;
selectkeys = dtglobal:Key << get values;
selectkeys = selectkeys[selectrows];
dt1 << Select Where( Contains( Matrix( selectkeys ), :Key ) );
dt2 << Select Where( Contains( Matrix( selectkeys ), :Key ) );
dt3 << Select Where( Contains( Matrix( selectkeys ), :Key ) );
)
;

Re: In what way would it be possible to apply the same data filter to multiple tables dynamically?

ian_jmp — Mon, 03 Nov 2014 20:06:02 GMT

The JSL at:

http://support.sas.com/demosdownloads/downarea_t4.jsp?productID=109646

is rather old and (thankfully!) obsolete since we added the 'row state handler' (in JMP 10 I think). This has a variety of uses, and the code below shows how you might exploit it in this case.

// ian.cox@jmp.com: 03Nove2014

// Demo of how to link multiple child tables to a parent table using a row state handler

NamesDefaultToHere(1);

// Make some tables to use

dt1 = NewTable("Table 1",

NewColumn("ID", Numeric, Conntinuous, Values((1::10)`)),

NewColumn("Response", Numeric, Continuous, Formula(RandomNormal(0,1)))

);

dt2 = NewTable("Table 2",

NewColumn("ID", Numeric, Conntinuous, Formula(RandomInteger(1,5))),

NewColumn("Attribute 2", Numeric, Continuous, Formula(RandomInteger(0,10))),

AddRows(20)

);

dt3 = NewTable("Table 3",

NewColumn("ID", Numeric, Conntinuous, Formula(RandomInteger(1,11))),

NewColumn("Attribute 3", Numeric, Continuous, Formula(RandomInteger(0,100))),

AddRows(30)

);

// Utility function:

// Given a data table, a column therein and some value(s) in that column, selects all corresponding rows.

// Need to allow for 'col' to be numeric (character), in which case 'vals' is a matrix (is a list).

selectMatchingRows =

Function({dt, col, vals},

rows2select = [];

if (IsMatrix(vals),

// 'col' is numeric

For (i=1, i<=NRow(vals), i++,

rows2select = VConcat(rows2select, dt << GetRowsWhere(col == vals[i]));

)

// 'col' is character

For (i=1, i<=NItems(vals), i++,

rows2select = VConcat(rows2select, dt << GetRowsWhere(col == vals[i]));

)

);

dt << SelectRows(rows2select);

);

// Make a row state handler to be assigned to the 'master' table (dt1). Allow for multiple row selections.

propagateSelectionToOtherTables =

Function({x},

// Get the rows that have been selected

selectedRows = dt1 << GetSelectedRows;

If(NRow(selectedRows) > 0,

// Get the corresponding IDs

IDs = dt1:ID[selectedRows];

// Select the corresponding rows in dt2

selectMatchingRows(dt2, Expr(:ID), IDs);

// Select the corresponding rows in dt3

selectMatchingRows(dt3, Expr(:ID), IDs);

// Clear any existing selection

dt2 << ClearSelect;

dt3 << ClearSelect;

);

// Assign the handler to dt1

rsh = dt1 << MakeRowStateHandler(propagateSelectionToOtherTables);