How to create a new field from existing field values

[stevenbutterwor]

I have a field with values similar to this: TagName=15PI008_15

The _15 portion of this value is the part I need to extract into a new field called "WellNumber". However, I need to transform this value into something else that makes more sense to the user e.g.

_15 = H315
_3 = H305
_19 = H319

etc.

How can I do this in search and is there a way of doing this by default?

[gcusello · Accepted solution]

Hi stevenbutterworth,
you can move in two ways depending by the number of values you have, in other words, you have anyway to extract the WellNumber using rex command

| rex field=your_field "_(?<WellNumber>\d+)"

then if you have few different values, you can use eval command

| eval New_field=case(WellNumber="15","H315",WellNumber="3","H305",WellNumber="19","H319")

if instead you have many different values, you can build a lookup containing the correlation values:

WellNumber,New_field
15,H315
3,H305
19,H319

and use lookup command

| lookup my_lookup.csv WellNumber OUTPUT New_field

Bye.
Giuseppe

[stevenbutterwor]

Thanks Giuseppe

I did have something like this in mind but wasn't sure of the execution method.

It works great now.

Steve

[richgalloway]

To do it in a search, first extract WellNumber using rex.

... | rex field=TagName "_(?<WellNumber>\d+)" |

Then transform WellNumber using either case or lookup

... | case WellField=case(WellNumber==15,"H315", WellNumber==3, "H305", WellNumber==19, "H319", 1==1, WellNumber) | ...

... | lookup WellNumbers.csv WellNumber OUTPUT WellField | ...

I'd use case if the number of options is small and doesn't change often; otherwise, use a lookup.

[drebai]

 | replace "_*" WITH "H3*" IN WellNumber

I'm not sure it's what u want.
Would you rather do it directly from extraction?

[cmerriman]

try something like this:

|rex field=fieldname "_(?<WellNumber>\d+)"|eval WellNumber="H3".substr("00".WellNumber,-2)