BookmarkSubscribe
Choose Language Hide Translation Bar
adam
Community Trekker

Find and Label Column

Hi,

 

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.

 

Thank you.

 

ID1FAIL1ID2FAIL2
AB11A972C99NFABV1A9781NN3 
AB11AB7DDS8NFABK1AB7E8186 
AB11AB7J9945FABD1E974C2FM 
AB11AB7K2PLRFABN1E275SV4E 
AB11AC70J01HFABD1E977ZMSL 
AB11AC70J10VFABJ1AB750FYP 
AB11AC70J120FABP1E270SWJH 
AB11AC70J1C1FABU1E36194WM 
AB11AC70J1ZRFABW1AC62A4RY 
AB11AC70J22CFAB91EB6DCMFM 
AB11AC70J33TFABD1A667K0FR 
AB11AC70J394FABM1A67KKNUK 
AB11AC70J4Z5FAB81A37PN5SH 
AB11AC70J5ULFAB81E87AYYUC 
AB11AC70J681FABW1AC62A93N 
AB11AC70J7CSFAB21A47N49XU 
AB11AC70J8Z4FABP1EB6D12XK 
AB11AC70J9R2FAB11AC70J22CF
AB11AC70JA3DFAB21AC77P9HA 
AB11AC70JC6AFABJ1AB79ULEV 
AB11AC70JCN6FABU1E879EMAV 
AB11AC70JDDHFABD1A667K70L 
AB11AC70JDFJFABH1AB71A2UX 
AB11AC70JEPYFAB11AC70J9R2F
0 Kudos
2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Find and Label Column

Assuming that your ID columns actually have a "#" in the name, the following script will do the trick

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"
	);
);
Jim
Highlighted
txnelson
Super User

Re: Find and Label Column

Here is another methodology

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 )
);
Jim
4 REPLIES 4
txnelson
Super User

Re: Find and Label Column

Assuming that your ID columns actually have a "#" in the name, the following script will do the trick

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"
	);
);
Jim
adam
Community Trekker

Re: Find and Label Column

Hi Jim,


Thanks a lot..!
It's working as expected.

0 Kudos
adam
Community Trekker

Re: Find and Label Column

Hi Jim,

 

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.

Thanks.

0 Kudos
Highlighted
txnelson
Super User

Re: Find and Label Column

Here is another methodology

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 )
);
Jim