lookup two csv pattern match query

[surekhasplunk]

I have a requirement like this

from file1.csv lookup file i am getting 2 fields
field1 field2

---

"application support" 1

"technology maintenance" 3
"enterprise platform" 4

file2.csv file has many fields out of which one of the field name field3 has data like this

filed3

"application file"
"cto maintenance"
"application file"
"application file"
"application file"
"enterprise security"
"enterprise security"

Now if field1="application support" search for word "application" in file2.csv in field3 And since we got a pattern match for word application now i need the count saying how many "application file" values are there in field3 of file2.csv and get the resulting table as below.

field1 field2 field3

---

"application support" 1 4
"technology maintenance" 3 1
"enterprise platform" 4 2

[anjambha · Accepted solution]

Hello,

try this..

| inputlookup file1 | join type=outer field1 [| inputlookup file2 | eval field1=case(field3 == "application file", "application support", field3 == "cto maintenance", "technology maintenance") | stats count by field1] | table field1 field2 count

[surekhasplunk]

Thank you very much @anjambha it worked

[elliotproebstel]

Couple of questions:
(1) Is this something you're doing once and could handle a multi-step process, or something you want to run over and over?
(2) How many entries are in file1? A small handful, or a very long list?
(3) In all of your examples, the position of the word in field1 from file1.csv matches the position of matching words in field3. For example, "application support" matches against "application file" - would it also match against "file application" if such an entry existed in field3?