topic Re: how to use rex function different different pattern of data in Knowledge Management

how to use rex function different different pattern of data

shishirkumar — Tue, 29 Sep 2020 23:42:56 GMT

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

Re: how to use rex function different different pattern of data

woodcock — Sat, 16 Mar 2019 21:04:03 GMT

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/\.[^\.]+$//"

Re: how to use rex function different different pattern of data

rvany — Sat, 16 Mar 2019 21:57:25 GMT

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.

Re: how to use rex function different different pattern of data

woodcock — Sat, 16 Mar 2019 22:14:52 GMT

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