Find string from one value in a column of different values in a dataset

Question

Hi everyone,

I am looking for a way to find if one of the strings contained into one column in a dataset (125 different entries in that said column, so 125 different strings to be searched) is contained into another data set (multi row/column search, like 70k rows with 40+ columns) and if found to return an info about the row in which it was found (a unique identifier is present in the second dataset).

TLDR : looking for a way to find one of 125 strings contained in a single column in Dataset_1 in a Dataset_2 with 70k row/40+ columns

Thanks for any help you can give!

bouhayao · Answer

Hello Gawa,

Thanks for the solution, sadly while the first dataset I have to use is 125 (doable), there is another with 19K+ entries haha...

Regards,

Gawa · Answer

hi @bouhayao

I can propose a kind of brute force approach:

1) Transpose Dataset1 into vertical shape (key column shall be unique ID if present)

2) Append Dataset 2 (that has 125 records)

3) Filter the record by Contains([Value],[Find])

# of records from Append Fields tool would be 70k x 40 x 125 = 350 millions. AMP engine should be ON to process data faster.