topic Re: Find and Label Column in Discussions
https://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49707#M28278
<P>Assuming that your ID columns actually have a "#" in the name, the following script will do the trick</P>
<PRE><CODE class=" language-jsl">Names Default To Here( 1 );
dt = Current Data Table();
For( i = 1, i <= N Rows( dt ), i++,
FoundRows = dt << get rows where( :Name( "ID2#" ) == :Name( "ID1#" )[i] );
If( N Rows( FoundRows ) > 0,
:Fail2[FoundRows] = "F"
);
);</CODE></PRE>Tue, 16 Jan 2018 06:11:43 GMTtxnelson2018-01-16T06:11:43ZFind and Label Column
https://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49706#M28277
<P>Hi,</P><P> </P><P>I have 2 columns, ID1 # and ID2# as shown below and both are not in orderly manner. I would like to know how can I use ID1# which is recorded as F(in FAIL1 column) and find the same number in ID2# and then label as F in FAIL2 column as shown in example below.</P><P> </P><P>Thank you.</P><P> </P><TABLE><TBODY><TR><TD>ID1</TD><TD>FAIL1</TD><TD>ID2</TD><TD>FAIL2</TD></TR><TR><TD>AB11A972C99N</TD><TD>F</TD><TD>ABV1A9781NN3</TD><TD> </TD></TR><TR><TD>AB11AB7DDS8N</TD><TD>F</TD><TD>ABK1AB7E8186</TD><TD> </TD></TR><TR><TD>AB11AB7J9945</TD><TD>F</TD><TD>ABD1E974C2FM</TD><TD> </TD></TR><TR><TD>AB11AB7K2PLR</TD><TD>F</TD><TD>ABN1E275SV4E</TD><TD> </TD></TR><TR><TD>AB11AC70J01H</TD><TD>F</TD><TD>ABD1E977ZMSL</TD><TD> </TD></TR><TR><TD>AB11AC70J10V</TD><TD>F</TD><TD>ABJ1AB750FYP</TD><TD> </TD></TR><TR><TD>AB11AC70J120</TD><TD>F</TD><TD>ABP1E270SWJH</TD><TD> </TD></TR><TR><TD>AB11AC70J1C1</TD><TD>F</TD><TD>ABU1E36194WM</TD><TD> </TD></TR><TR><TD>AB11AC70J1ZR</TD><TD>F</TD><TD>ABW1AC62A4RY</TD><TD> </TD></TR><TR><TD>AB11AC70J22C</TD><TD>F</TD><TD>AB91EB6DCMFM</TD><TD> </TD></TR><TR><TD>AB11AC70J33T</TD><TD>F</TD><TD>ABD1A667K0FR</TD><TD> </TD></TR><TR><TD>AB11AC70J394</TD><TD>F</TD><TD>ABM1A67KKNUK</TD><TD> </TD></TR><TR><TD>AB11AC70J4Z5</TD><TD>F</TD><TD>AB81A37PN5SH</TD><TD> </TD></TR><TR><TD>AB11AC70J5UL</TD><TD>F</TD><TD>AB81E87AYYUC</TD><TD> </TD></TR><TR><TD>AB11AC70J681</TD><TD>F</TD><TD>ABW1AC62A93N</TD><TD> </TD></TR><TR><TD>AB11AC70J7CS</TD><TD>F</TD><TD>AB21A47N49XU</TD><TD> </TD></TR><TR><TD>AB11AC70J8Z4</TD><TD>F</TD><TD>ABP1EB6D12XK</TD><TD> </TD></TR><TR><TD>AB11AC70J9R2</TD><TD>F</TD><TD>AB11AC70J22C</TD><TD>F</TD></TR><TR><TD>AB11AC70JA3D</TD><TD>F</TD><TD>AB21AC77P9HA</TD><TD> </TD></TR><TR><TD>AB11AC70JC6A</TD><TD>F</TD><TD>ABJ1AB79ULEV</TD><TD> </TD></TR><TR><TD>AB11AC70JCN6</TD><TD>F</TD><TD>ABU1E879EMAV</TD><TD> </TD></TR><TR><TD>AB11AC70JDDH</TD><TD>F</TD><TD>ABD1A667K70L</TD><TD> </TD></TR><TR><TD>AB11AC70JDFJ</TD><TD>F</TD><TD>ABH1AB71A2UX</TD><TD> </TD></TR><TR><TD>AB11AC70JEPY</TD><TD>F</TD><TD>AB11AC70J9R2</TD><TD>F</TD></TR></TBODY></TABLE>Tue, 16 Jan 2018 04:58:44 GMThttps://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49706#M28277adam2018-01-16T04:58:44ZRe: Find and Label Column
https://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49707#M28278
<P>Assuming that your ID columns actually have a "#" in the name, the following script will do the trick</P>
<PRE><CODE class=" language-jsl">Names Default To Here( 1 );
dt = Current Data Table();
For( i = 1, i <= N Rows( dt ), i++,
FoundRows = dt << get rows where( :Name( "ID2#" ) == :Name( "ID1#" )[i] );
If( N Rows( FoundRows ) > 0,
:Fail2[FoundRows] = "F"
);
);</CODE></PRE>Tue, 16 Jan 2018 06:11:43 GMThttps://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49707#M28278txnelson2018-01-16T06:11:43ZRe: Find and Label Column
https://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49708#M28279
<P>Hi Jim,</P><P><BR />Thanks a lot..!<BR />It's working as expected.</P>Tue, 16 Jan 2018 07:18:24 GMThttps://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49708#M28279adam2018-01-16T07:18:24ZRe: Find and Label Column
https://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49709#M28280
<P>Hi Jim,</P><P> </P><P>I have one more question. In my real case my ID2# has about 70K rows but the ID1# has only about 3K rows. When I ran the script, it's like taking more than 30minutes to complete :-) Is there any faster way to shorten the duration.</P><P>Thanks.</P>Tue, 16 Jan 2018 09:17:28 GMThttps://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49709#M28280adam2018-01-16T09:17:28ZRe: Find and Label Column
https://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49715#M28284
<P>Here is another methodology</P>
<PRE><CODE class=" language-jsl">Names Default To Here( 1 );
dt = Current Data Table();
dt2 = dt << subset( Invisible, selected rows( 0 ), columns( :Name( "ID1#" ), :Fail1 ) );
dt2:Fail1 << set name( "FAIL2" );
dt = dt << Update(
with( dt2 ),
Update,
By Matching Columns( :Name( "ID2#" ) = :Name( "ID1#" ) ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 0 ),
Preserve main table order( 1 )
);</CODE></PRE>Tue, 16 Jan 2018 13:32:40 GMThttps://community.jmp.com/t5/Discussions/Find-and-Label-Column/m-p/49715#M28284txnelson2018-01-16T13:32:40Z