create category filed issue

[alec1982]

hi,

I have a filed that has

fieldName

sales\layer 1\abc

sales\layer 1\2 \test

calls\layer 1 testing

prod\layer 3\abc

I need a field that shows

layer 1

layer 1\2

layer 1

layer 3

tried wildmatch but run into issue because of having "1/2"

[sunny_talwar]

May be this:

Table:

LOAD *,

  Mid(fieldName, Index(fieldName, '\', 1)+1, Index(fieldName, '\', -1)-Index(fieldName, '\', 1)-1) as NewField;

LOAD * Inline [

fieldName

sales\layer 1\abc

sales\layer 1\2 \test

calls\layer 1\testing

prod\layer 3\abc

];

Note: I assumed that you forget '\' for the third row. If there is a '\', then this solution will have to be altered further.

(calls\layer 1\testing)

[maxgro]

why layer 1 at the second row?

load

  *,

  if(f1<>f2, mid(field, f1+1, f2-f1-1)) ;

load

  field,

  index(field, '\', 1) as f1,

  index(field, '\', -1) as f2

inline [

field

sales\layer 1\abc

sales\layer 1\2 \test

calls\layer 1 testing

prod\layer 3\abc

];

[alec1982]

thank you all..

actually the field I want to use has the following look

FiledName

AVC - Test Layer 3

Aed Presentation Layer

Adf Layer2

DEF Layer 1/2

QWE Layer 1

the result needs to be

Layer 3

Presentation Layer

Layer 2

Layer 1/2

Layer 1

[sunny_talwar]

Is there a logic of how you would select the new field? You picked Presentation, but did not pick Test? Just trying to understand the common theme here

[alec1982]

the word presentation will always be there similar to how I added it

[sunny_talwar]

No i meant that

FiledName                         NewFieldName               Notes

AVC - Test Layer 3               Layer 3                        Test was not picked

Aed Presentation Layer          Presentation Layer       Presentation was

From the data it seemed that first word would always be Layer, but Presentation was an exception. Now assuming this was just a sample and the real data might have more exceptions, I am looking for some kind of common theme to be able to code it so that we can derive desired result. Hope what I just said make sense.

[alec1982]

thank you Sunny.. this is the only exception.

We will always have the wording the same all over the data..

FiledName

AVC - Test Layer 3

Aed Presentation Layer

Adf Layer2

DEF Layer 1/2

QWE Layer 1

[sunny_talwar]

From what you are saying, It seems like these are the only 5 values that FieldName would ever take. If that's true, may be you just need a mapping load or Pick(Match()) to designate a new value to your existing value. Is my understanding correct?

[alec1982]

actually no. the values Presentation Layer, Layer 1, Layer 1/2, Layer 2, Layer 3 and Presentation Layer wording will always exist on the same location within the string..

the other values within the same string will never be the same..