Find and Label Column

Question

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.

ID1FAIL1ID2FAIL2AB11A972C99NFABV1A9781NN3 AB11AB7DDS8NFABK1AB7E8186 AB11AB7J9945FABD1E974C2FM AB11AB7K2PLRFABN1E275SV4E AB11AC70J01HFABD1E977ZMSL AB11AC70J10VFABJ1AB750FYP AB11AC70J120FABP1E270SWJH AB11AC70J1C1FABU1E36194WM AB11AC70J1ZRFABW1AC62A4RY AB11AC70J22CFAB91EB6DCMFM AB11AC70J33TFABD1A667K0FR AB11AC70J394FABM1A67KKNUK AB11AC70J4Z5FAB81A37PN5SH AB11AC70J5ULFAB81E87AYYUC AB11AC70J681FABW1AC62A93N AB11AC70J7CSFAB21A47N49XU AB11AC70J8Z4FABP1EB6D12XK AB11AC70J9R2FAB11AC70J22CFAB11AC70JA3DFAB21AC77P9HA AB11AC70JC6AFABJ1AB79ULEV AB11AC70JCN6FABU1E879EMAV AB11AC70JDDHFABD1A667K70L AB11AC70JDFJFABH1AB71A2UX AB11AC70JEPYFAB11AC70J9R2F

txnelson · Accepted Answer

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

txnelson · Accepted Answer

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