topic Re: Issue with Subfield in QlikView

Issue with Subfield

Anonymous — Tue, 02 May 2017 08:22:29 GMT

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

Re: Issue with Subfield

antoniotiman — Tue, 02 May 2017 08:49:42 GMT

Hi,

try

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

Regards,

Antonio

Re: Issue with Subfield

prieper — Tue, 02 May 2017 08:57:34 GMT

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

Re: Issue with Subfield

senpradip007 — Tue, 02 May 2017 09:19:48 GMT

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

];

Re: Issue with Subfield

Anonymous — Tue, 02 May 2017 09:33:07 GMT

we need AAA BBB CC EEE as one field value

Re: Issue with Subfield

prieper — Tue, 02 May 2017 09:46:59 GMT

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.

Re: Issue with Subfield

PrashantSangle — Tue, 02 May 2017 09:47:43 GMT

Hi,

1st remove space from field value then try.

Regards,

Re: Issue with Subfield

MindaugasBacius — Tue, 02 May 2017 09:49:37 GMT

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

Re: Issue with Subfield

antoniotiman — Tue, 02 May 2017 09:52:11 GMT

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

Re: Issue with Subfield

senpradip007 — Tue, 02 May 2017 10:19:15 GMT

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

];

Re: Issue with Subfield

el_aprendiz111 — Tue, 02 May 2017 14:55:33 GMT

Hi,

LET v_chr= '-_><,./?+=)(*&^%$#@!|\`~';

LOAD *,
SubField(purgechar(Blank, '$(v_chr)'), ' ',1) & ' ' &
SubField(purgechar(Blank, '$(v_chr)'), ' ',2) & ' ' &
SubField(purgechar(Blank, '$(v_chr)'), ' ',3) & ' ' &
SubField(purgechar(Blank, '$(v_chr)'), ' ',5) AS New_Field

Inline [
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
];

EXIT Script;