Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
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)
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
];
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
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
the word presentation will always be there similar to how I added it
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.
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
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?
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..