Remove last values of a field result

[n4niyaz]

following are the output of a filed

file=a.csv
file=a1.csv
file=a2.csv
file=b.csv
file=b1.csv

What i required is while executing |stats count by file i need following result
a=3 and b=2
is there any way to get this result

[elliotproebstel · Accepted solution]

You can use this to get the first character of the file name:

| eval file=substr(file,0,1)

So insert that directly before your call to

| stats count by file

and you should get the desired result.

[n4niyaz]

above looking good but suppose if the the field name contains date and time like below

file=a_2017-09-16_12:00:00.csv
file=a_2017-09-17_12:00:00.csv
file=b.csv
file=b1.csv
file=b_2017-09-17_12:00:00.csv

|stats count by file gives a=2 and b=2 and b1 =1

Can i get this result using regex

[elliotproebstel]

Sure. If you want the category of file to be "everything that comes before either a period or an underscore", then this will work:

|rex mode=sed field=file "s/([^_\.]+)(.*)/\1/"

[n4niyaz]

Thanks @elliotproebstel I used mvindex(split) command so it works fine ie

|eval test=mvindex(split(file,_201),0) so i get the result now.