Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Find and Label Column

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 15, 2018 8:58 PM
(2288 views)

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.

ID1 | FAIL1 | ID2 | FAIL2 |

AB11A972C99N | F | ABV1A9781NN3 | |

AB11AB7DDS8N | F | ABK1AB7E8186 | |

AB11AB7J9945 | F | ABD1E974C2FM | |

AB11AB7K2PLR | F | ABN1E275SV4E | |

AB11AC70J01H | F | ABD1E977ZMSL | |

AB11AC70J10V | F | ABJ1AB750FYP | |

AB11AC70J120 | F | ABP1E270SWJH | |

AB11AC70J1C1 | F | ABU1E36194WM | |

AB11AC70J1ZR | F | ABW1AC62A4RY | |

AB11AC70J22C | F | AB91EB6DCMFM | |

AB11AC70J33T | F | ABD1A667K0FR | |

AB11AC70J394 | F | ABM1A67KKNUK | |

AB11AC70J4Z5 | F | AB81A37PN5SH | |

AB11AC70J5UL | F | AB81E87AYYUC | |

AB11AC70J681 | F | ABW1AC62A93N | |

AB11AC70J7CS | F | AB21A47N49XU | |

AB11AC70J8Z4 | F | ABP1EB6D12XK | |

AB11AC70J9R2 | F | AB11AC70J22C | F |

AB11AC70JA3D | F | AB21AC77P9HA | |

AB11AC70JC6A | F | ABJ1AB79ULEV | |

AB11AC70JCN6 | F | ABU1E879EMAV | |

AB11AC70JDDH | F | ABD1A667K70L | |

AB11AC70JDFJ | F | ABH1AB71A2UX | |

AB11AC70JEPY | F | AB11AC70J9R2 | F |

2 ACCEPTED SOLUTIONS

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Find and Label Column

Hi Jim,

Thanks a lot..!

It's working as expected.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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