How to create a lookup matching non-exact words ?

[damode]

I have the below type of event and I want to add a category field to it using lookups

time    Transaction Business name
6/01/2018   40.22   ABC foods 6697    VALE  TAP AND PAY 0000
8/01/2018   45.22   supermarket suburb TAP and PAY 0000

So, I created the following lookup - test.csv

Business name,Category
ABC foods 6697    VALE  TAP AND PAY 0000,Dine out
DEF utilities,Utilities
TARGET suburb name,Shopping
supermarket suburb TAP and PAY 0000,Groceries

Below is my search query,

index="finance" sourcetype="csv_finance" | lookup test.csv "Business name" OUTPUT Category|  table "Business name" Category

but its not displaying the results.
How can I create a successful lookup that will display the categories along with the business name in the search results ?

[micahkemp · Accepted solution]

You can do wildcard matching in your lookup. And to avoid confusion I'd use _ instead of in your field names in the lookup.

transforms.conf:

[test]
filename = test.csv
match_type = WILDCARD(Business_name)

test.csv:

 Business_name,Category
 ABC*,Dine out
 DEF*,Utilities
 TARGET*,Shopping
 supermarket*,Groceries

And when searching:

index="finance" sourcetype="csv_finance" | lookup test Business_name AS "Business name" OUTPUT Category|  table "Business name" Category

[damode]

Thanks @micahkemp!

[micahkemp]

Does your lookup table have the numbers at the front of the line? It's displayed with two leading numbers, which seems to indicate the 2nd number per line is actually in your file.

[damode]

No, the lookup table doesnt have any numbers. Sorry, I got the formatting wrong while posting this qn. I will update it.

[micahkemp]

It's also unclear from your posted data what the values of each field are in the event. Is the Business Name ABC foods 6697 VALE TAP AND PAY 0000?

[damode]

Yes, thats right.
the data is of bank transactions in csv file format.