257605_en-US

liliconcom

Select Actors with Multiple Criteria

My data has multiple observations from the same Actor.  I want to find Actors that satisfy criteria based on multiple columns - but would satisfy different criteria in different observations of the same Actor.

 

Example:

 

I want to find Actors that satisfy:
Size < 250

AND

For Competition A : Score 1 > 10 AND Score 2 > 10

AND

For Competition B : Score 1 > 10 AND Score 2 > 40

AND

For Competition C : Score 3 > 50

 

So for the above table it should tell me Actor B.

Thanks!

Re: Select Actors with Multiple Criteria

Hi,

 

One way to do this is to successively intersect Associative Arrays, as below.

 

Cheers,

Brady

 

Names Default To Here( 1 );
dt = New Table( "Example",
 Add Rows( 9 ),
 New Column( "Actor", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 1, 2, 3, 1, 2, 3] ) ),
 New Column( "Competition", Character, "Nominal", Set Values( {"A", "A", "A", "B", "B", "B", "C", "C", "C"} ) ),
 New Column( "Score 1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [12, 13, 20, 25, 15, 16, 16, 15, 12] ) ),
 New Column( "Score 2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [12, 46, 45, 25, 48, 34, 59, 45, 56] ) ),
 New Column( "Score 3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [45, 50, 55, 60, 45, 80, 52, 61, 43] ) ),
 New Column( "Size", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [100, 200, 300, 100, 200, 300, 100, 200, 300] ) )
);

critExprList = {
 Expr( :competition == "A" & :Score1 > 10 & :Score 2 > 10), 
 Expr( :competition == "B" & :Score1 > 10 & :Score 2 > 40),
 Expr( :competition == "C" & :Score3 > 50)
};

ansAA = Associative Array( dt:actor[dt << get rows where( :size < 250 )] );

For( i = 1, i <= N Items( critExprList ), i++,
 Eval(
  Substitute( Expr( curAA = Associative Array( dt:actor[dt << get rows where( _CLAUSE_ )] ) ), Expr( _CLAUSE_ ), eval( critExprList[i] ) )
 );
 ansAA << Intersect (curAA)
);

dt << select  where(contains(ansAA << get keys, :actor));

Re: Select Actors with Multiple CriteriaThanks for responding!
Yes, that's the work-around I've used, but I'm curious if there's a way within the same data table, rather than Splitting to a new one.
It's possible it can't be done in the current structure, but I'm not experienced enough to know.Re: Select Actors with Multiple Criteria

My solution on how to do this selection is by reshaping the data table into one row per actor and then using the required selection criteria having all the data per actor available.

Names Default To Here( 1 );
dt = New Table( "Example",
 Add Rows( 9 ),
 New Column( "Actor", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 1, 2, 3, 1, 2, 3] ) ),
 New Column( "Competition", Character, "Nominal", Set Values( {"A", "A", "A", "B", "B", "B", "C", "C", "C"} ) ),
 New Column( "Score 1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [12, 13, 20, 25, 15, 16, 16, 15, 12] ) ),
 New Column( "Score 2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [12, 46, 45, 25, 48, 34, 59, 45, 56] ) ),
 New Column( "Score 3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [45, 50, 55, 60, 45, 80, 52, 61, 43] ) ),
 New Column( "Size", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [100, 200, 300, 100, 200, 300, 100, 200, 300] ) )
);
dtSplit = dt << Split(
 Split By( :Competition ),
 Split( :Score 1, :Score 2, :Score 3, :Size ),
 Group( :Actor ),
 Sort by Column Property
);

dtSplit << select where( (:Size A < 250 & :Score 1 A > 10 & :Score 2 A > 10 &
 :Score 1 B > 10 & :Score 2 B > 40 & :Score 3 C > 50) |
 (:Size B < 250 & :Score 1 A > 10 & :Score 2 A > 10 &
 :Score 1 B > 10 & :Score 2 B > 40 & :Score 3 C > 50) |
 (:Size C < 250 & :Score 1 A > 10 & :Score 2 A > 10 &
 :Score 1 B > 10 & :Score 2 B > 40 & :Score 3 C > 50) 
);

If(N Rows(dtSplit << get selected rows) > 0, Dialog("The Actor(s) is/are " || char( dtSplit<<GET selected="" rows=""></GET>

Re: Select Actors with Multiple CriteriaThanks for your reply!
Unfortunately, that doesn't work, because if I say, "Competition = A AND (other A criteria) AND Competition = B" it breaks, because Competition can't = A & B. But if I say, Competition = A AND (other A criteria) OR Competition = B AND (other B criteria), it gives me Actors that do well ANY of the Competitions, but not ALL of them.
Sorry it's a bit hard to explain - the problem arises from the Score 1, 2, and 3 columns containing results from different Competitions. One work-around is to Split the table, saying 'Competition A Score 1' etc as the columns, but I'm looking for a solution within the current data table.Re: Select Actors with Multiple Criteria

Select Rows > Data Filter. Select each column used in the criterion and add them one at a time to the filter. You can then set the desired level for each of these variables. The Data Filter affects the row states in the current data table.

 

See Help > JMP Documentation Library > Using JMP guide for details and examples.