How to lookup field from csv file using automatic lookups?

[dhavamanis]

We have indexed csv file and it has field brand_id, can you please provide steps how to lookup this brand_id field equivalent to brand_name using automatic lookup in the output.

Input data (csv) :

brand_id,field1,field2,field3
1,11,111,1111
2,22,222,2222
3,33,333,3333
1,22,222,2222
2,11,111,1111
1,11,111,1111

Required output (brandwise stats count):

brand_id,brand_name,count
1,xyz,3
2,abc,2
3,pqr,1

[strive · Accepted solution]

Follow below steps

Step 1: Add CSV file containing columns Brand_ID, Brand_Name under lookups folder

Step 2: Add a Stanza in your transforms.conf file like this
[csv_brand_info]
filename = brand_info.csv
max_matches = 1
case_sensitive_match = false

Step 3: Write search to include lookup like this

your search.. | stats count as Count by brand_id | lookup  csv_brand_info Brand_ID as brand_id OUTPUT Brand_ID Brand_Name | table Brand_ID Brand_Name Count

[somesoni2]

Provided you have already configured a lookup table file with fields "brand_id" and "brand_name", with name say brandlookup.csv, then try this

index=yourindex source=yourcsvfile  | stats count by brand_id | lookup brandlookup.csv brand_id OUTPUT brand_name | table brand_id, brand_name, count

[somesoni2]

The case of the field name matters. If the field name is 'brand_id' , use the same during lookup. (I see the case is different for brand_id, also ensure case for brand_name)

[dhavamanis]

Thanks if we try this,

index="idxmember" | stats count by brand_id | lookup lufile_brandid_brandname.csv BRAND_ID OUTPUT BRAND_NAME | table brand_id, BRAND_NAME, count

we are not getting the BRAND_NAME in the output.