how to use rex function different different pattern of data

[shishirkumar]

In my scenario data filename having different different of pattern :

Sample filename data :
File_Name | Client_name (Output column)
Weekly OB Unable to Reach Report-011919-012619-Absolute Total Care.xlsx | Absolute Total Care
OB Incentive Report-December 2018-WellCare of Georgia.xlsx | WellCare of Georgia
Optum_WellCareNJ_Quarterly_AssessmentResponse_2018Q4.xlsx | Optum
WellCareSC Qrtrly_Visits_2018Q4.xlsx | WellCareSC **
TotalCare_Dashboard_201812_V1.pdf |TotalCare**

In Above data we have File_Name and Client name

Idea is : To extract Client Name from File name

to do this I have Lookupfile where client names are stored

[woodcock]

Like this:

| makeresults 
| eval File_Name="Weekly OB Unable to Reach Report-011919-012619-Absolute Total Care.xlsx|OB Incentive Report-December 2018-WellCare of Georgia.xlsx|Optum_WellCareNJ_Quarterly_AssessmentResponse_2018Q4.xlsx|WellCareSC Qrtrly_Visits_2018Q4.xlsx|TotalCare_Dashboard_201812_V1.pdf"
| makemv delim="|" File_Name
| mvexpand File_Name

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval hyphen_based = File_Name
| rex field=hyphen_based mode=sed "s/^.*-//"
| eval File_Name = if(File_Name != hyphen_based, hyphen_based, replace(File_Name, "[_ ].*$", ""))
| rex field=File_Name mode=sed "s/\.[^\.]+$//"

[rvany]

This won't work as the sed part deletes all characters before and including the last dash. The line starting with "Optum" e.g. has no dash at all so the complete file_name is returned.

I like the idea of using a lookup file to check the file_name against it - but the lookup command does only an exact string match. Is there some way to

Expanding your way of creating test data I got the following:

| makeresults 
| eval file_name="Weekly OB Unable to Reach Report-011919-012619-Absolute Total Care.xlsx|OB Incentive Report-December 2018-WellCare of Georgia.xlsx|Optum_WellCareNJ_Quarterly_AssessmentResponse_2018Q4.xlsx|WellCareSC Qrtrly_Visits_2018Q4.xlsx|TotalCare_Dashboard_201812_V1.pdf" 
| makemv delim="|" file_name 
| mvexpand file_name
| eval client_name="Absolute Total Care|WellCare of Georgia|Optum|WellCareSC|TotalCare"
| makemv delim="|" client_name
| mvexpand client_name
| where file_name like "%".client_name."%"

Maybe this could lead in the right direction.

[woodcock]

Quite right. I have made more adjustments; see my updated answer which works for every file.