How to find keywords in a table when you cannot connect directly to the server

[casey893]

Hi,

I am trying to find a set of keywords within a certain column of my table in order to filter out a list of acquirers from transaction data. 

I am not able to connect directly to the Hive server so I have to use this workaround. Does anyone know how I can still use a text input tool to connect my data to the text? Or is there another way to do this?

I have attached what I have so far in the workflow. 

Connect in DB (file attachment to server) -> Data stream out -> Select

Thank you! 

[Luke_C]

Hi @casey893 

If I understand correctly, your screenshot represents the data that you want to check against a list of keywords. What you could do is create a list of the keywords, either in a file (excel, text,etc) or a text input. Then you could use a find and replace tool to check those keywords against the column in your data. 

I'd recommend using the append option and appending the keyword field that way you can filter on the records that do or do not contain keywords.

If you have some sample data I could mock up an example if you're unfamiliar with the tool's functionality.

[casey893]

Hi Luke, 

Thank you for your response!

I am wanting to look at the transaction description in my data set, example:

Payment from James

Bank of Bob Merchant Services

Transfer from Billy

and find all the transaction descriptions that contain "MERCHANT","MERCH" "MRCH" "MER"

My original attachment shows how I am connected to the table of data that I want to search in. 

[Luke_C · Accepted solution]

Hi @casey893 

Here's a simple example using your data:

1. Data table represents your data from the DB
2. Keywords is the list of keywords provided.
3. Find and replace tool appends a column identifying what keyword is found (null if not found).
   1. Assumption: this is case insensitive.
4. Filter on keywords. True anchor is records containing keywords. F anchor is records without keywords.

[casey893]

Thank you, that worked!