topic Re: concatenate delete duplicates; select where - jmp list in Discussions

concatenate delete duplicates; select where - jmp list

anneg — Tue, 10 Apr 2012 09:01:16 GMT

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

Re: concatenate delete duplicates; select where - jmp list

ms — Tue, 10 Apr 2012 11:23:43 GMT

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.

Re: concatenate delete duplicates; select where - jmp list

anneg — Tue, 10 Apr 2012 12:21:41 GMT

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