Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hi,
try
If(Temp='Blank','Blank',Left(Temp,Index(Temp,' ',3)))
Regards,
Antonio
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
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
];
we need AAA BBB CC EEE as one field value
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.
Hi,
1st remove space from field value then try.
Regards,
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
If(Temp='Blank','Blank',Left(Replace(PurgeChar(Temp,'>-'),' ',' '),Index(Replace(PurgeChar(Temp,'>-'),' ',' '),' ',4)))
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
];