cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
anneg
Level I

concatenate delete duplicates; select where - jmp list


Hello,

this problem really puzzles me.

I want to make an concatenate but in the one table (dt2) I got also some entries I've got allready in the first one (dt1). I want to keep the ones in the first data Table (dt1) and want to delete them in the second one (dt2).

I allready tried and have another problem using the select where and delete the duplicates in the second table before concatenating. I have a List of all the the the duplicates, but I canot use the list as a variable in the select where clause nor the data filter


Summarize( a = by(column(dt, "band")),c=count );



The variable looks like this:

 

a = {"2_12_000264_0_9", "2_12_000265_0_1", "2_12_000267_0_3", "2_12_000269_1_3", "2_12_000269_2_1", "2_12_000270_1_7", "2_12_000270_2_5", "2_12_000271_0_1", "2_12_000272_1_9", "2_12_000272_2_7", "2_12_000273_1_1", "2_12_000273_2_8", "2_12_000274_0_4", "2_12_000275_1_3", "2_12_000275_2_1", "2_12_000276_1_4", "2_12_000276_2_2"};

and the complete data filter:


dt2 << Data Filter( Add Filter( columns( :Bandname ), Where( :Bandname == {"2_12_000264_0_9", "2_12_000265_0_1", "2_12_000267_0_3", "2_12_000269_1_3", "2_12_000269_2_1", "2_12_000270_1_7", "2_12_000270_2_5", "2_12_000271_0_1", "2_12_000272_1_9", "2_12_000272_2_7", "2_12_000273_1_1", "2_12_000273_2_8", "2_12_000274_0_4", "2_12_000275_1_3", "2_12_000275_2_1", "2_12_000276_1_4", "2_12_000276_2_2"}), Display( :Bandname, Size( 221, 259 ), List Display ), Mode( Show( 1 ) ) ));



But using the variable doesnt work:


Add Filter( columns( :Bandname ), Where( :Bandname == a ), Display( :Bandname, Size( 221, 259 ), List Display ), Mode( Show( 1 ) ) ));




why??

But the main problem is the above one, deleting duplicates before concatenating.

Thanks in Advance

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: concatenate delete duplicates; select where - jmp list

I don't know why, but in some cases JSL just don't resolve list variables even when a clear-text list is perfectly evaluated.

One way to get around this is to use an expression and substitute a parsed list into it before evaluation. Here's a data filter example:

dt = Open( "$ENGLISH_SAMPLE_DATA/Big Class.jmp" );

a = {"KATIE", "HENRY", "ROBERT"};

df_expr = Expr(dt << data filter(add filter( columns( :name ), where( :name==a ), Display( :name, List Display ))));

Substituteinto( df_expr, Expr( a ), Parse( Char( Eval( a ) ) ) ) ;

eval(df_expr);


However, to conditionally select rows based on a list may be scripted more efficiently using For Each Row(). The result of the code below should be identical to the above (except for the Data Filter Window).

dt = Open( "$ENGLISH_SAMPLE_DATA/Big Class.jmp" );

a = {"KATIE", "HENRY", "ROBERT"};

For Each Row( Row State( Row() ) = Selected State( Contains( a, :name[] ) ) )

Once the duplicate rows are selected, just run dt << delete rows before concatenating.

View solution in original post

2 REPLIES 2
ms
Super User (Alumni) ms
Super User (Alumni)

Re: concatenate delete duplicates; select where - jmp list

I don't know why, but in some cases JSL just don't resolve list variables even when a clear-text list is perfectly evaluated.

One way to get around this is to use an expression and substitute a parsed list into it before evaluation. Here's a data filter example:

dt = Open( "$ENGLISH_SAMPLE_DATA/Big Class.jmp" );

a = {"KATIE", "HENRY", "ROBERT"};

df_expr = Expr(dt << data filter(add filter( columns( :name ), where( :name==a ), Display( :name, List Display ))));

Substituteinto( df_expr, Expr( a ), Parse( Char( Eval( a ) ) ) ) ;

eval(df_expr);


However, to conditionally select rows based on a list may be scripted more efficiently using For Each Row(). The result of the code below should be identical to the above (except for the Data Filter Window).

dt = Open( "$ENGLISH_SAMPLE_DATA/Big Class.jmp" );

a = {"KATIE", "HENRY", "ROBERT"};

For Each Row( Row State( Row() ) = Selected State( Contains( a, :name[] ) ) )

Once the duplicate rows are selected, just run dt << delete rows before concatenating.

anneg
Level I

Re: concatenate delete duplicates; select where - jmp list

Thanks so much, this works great... and your first code is really good for understanding jsl