Issue with Subfield

[Anonymous]

Hi All

I have field which I need for futher field creation using

if( Temp='Blank','Blank',Trim(SubField(Temp,Mid(Temp,FindOneOf(Temp,'~!@#$%^&*()_+`-={}|[]\:<>?,./'),1),1)))  as Temp_New

where Temp_New will be AAA BBB CC

its working fine  for below list

AAA BBB CC - DDD - Bonds

AAA BBB CC - EEE - Credit

AAA BBB CC - EEE - FX PB

AAA BBB CC - EEE - Rates

its not able to work for such case with no separator

AAA BBB CC  EEE Credit

Temp

Blank

AAA BBB CC - DDD - Bonds

AAA BBB CC - EEE - Credit

AAA BBB CC - EEE - FX PB

AAA BBB CC - EEE - Rates

AAA BBB CC  DDD Bonds

AAA BBB CC  EEE Credit

AAA BBB CC  EEE FX PB

AAA BBB CC  EEE Rates

AAA BBB CC > EEE > Quantitative Alalytics

[antoniotiman]

Hi,

try

If(Temp='Blank','Blank',Left(Temp,Index(Temp,' ',3)))

Regards,

Antonio

[prieper]

If you always have the same fixed structure in the first couple of field, you may use a script like:

LOAD

    Field2Read,

    CleansedField,

    IF(CleansedField = 'Blank', 'Blank',

        LEFT(CleansedField, 3))                    AS Field2,

    IF(CleansedField = 'Blank', NULL(),

        MID(CleansedField, 4, 3))                AS Field3,

    IF(CleansedField = 'Blank', NULL(),

        MID(CleansedField, 9, 2))                AS Field4,

    IF(CleansedField = 'Blank', NULL(),

        MID(CleansedField, 12, 3))                AS Field5,

    IF(CleansedField = 'Blank', NULL(),

        MID(CleansedField, 16, 100))            AS Field6;

LOAD

    Field2Read,

    TRIM(PURGECHAR(Field2Read, '~!@#$%^&*()_+`-={}|[]\:<>?,./'))            AS CleansedField;

LOAD * INLINE [Field2Read

   Blank

    AAA BBB CC - DDD - Bonds

    AAA BBB CC - EEE - Credit

    AAA BBB CC - EEE - FX PB

    AAA BBB CC - EEE - Rates

    AAA BBB CC  DDD Bonds

    AAA BBB CC  EEE Credit

    AAA BBB CC  EEE FX PB

    AAA BBB CC  EEE Rates

    AAA BBB CC > EEE > Quantitative Alalytics

];

HTH Peter

[senpradip007]

Could you please elaborate your requirement? Based on the sample data you have shared go though  the following script.

load *, purgechar(Field, '-_><,./?+=)(*&^%$#@!|\`~') as NewField Inline [

  Field

    AAA BBB CC - DDD - Bonds

    AAA BBB CC - EEE - Credit

    AAA BBB CC - EEE - FX PB

    AAA BBB CC - EEE - Rates

    AAA BBB CC  DDD Bonds

    AAA BBB CC  EEE Credit

    AAA BBB CC  EEE FX PB

    AAA BBB CC  EEE Rates

    AAA BBB CC > EEE > Quantitative Alalytics

];

[Anonymous]

we need AAA BBB CC  EEE   as one field value

[prieper]

Do these fields have the same length?

Is there another identifyer? May these fields have multiple blanks? What about "DDD"?

Suggest you to provide some better example and the desired output.

[PrashantSangle]

Hi,

1st remove space from field value then try.

Regards,

[MindaugasBacius]

Could you please give us the result you wish to get from the data:

    AAA BBB CC - DDD - Bonds

    AAA BBB CC - EEE - Credit

    AAA BBB CC - EEE - FX PB

    AAA BBB CC - EEE - Rates

    AAA BBB CC  DDD Bonds

    AAA BBB CC  EEE Credit

    AAA BBB CC  EEE FX PB

    AAA BBB CC  EEE Rates

    AAA BBB CC > EEE > Quantitative Alalytics

[antoniotiman]

If(Temp='Blank','Blank',Left(Replace(PurgeChar(Temp,'>-'),' ',' '),Index(Replace(PurgeChar(Temp,'>-'),' ',' '),' ',4)))

[senpradip007]

Try like:

load *,

  subfield(NewField, '  ', 1) &' '& left(subfield(NewField, '  ', 2),3) as Test;

load *, purgechar(Field, '-_><,./?+=)(*&^%$#@!|\`~') as NewField Inline [

  Field

    AAA BBB CC - DDD - Bonds

    AAA BBB CC - EEE - Credit

    AAA BBB CC - EEE - FX PB

    AAA BBB CC - EEE - Rates

    AAA BBB CC  DDD Bonds

    AAA BBB CC  EEE Credit

    AAA BBB CC  EEE FX PB

    AAA BBB CC  EEE Rates

    AAA BBB CC > EEE > Quantitative Alalytics

];