Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shweta2608
Creator
Creator

Issue with Subfield

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
10 Replies
antoniotiman
Master III
Master III

Hi,

try

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

Regards,

Antonio

prieper
Master II
Master II

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
Specialist III
Specialist III

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

];

shweta2608
Creator
Creator
Author

we need AAA BBB CC  EEE   as one field value

prieper
Master II
Master II

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,

Great dreamer's dreams never fulfilled, they are always transcended.
MindaugasBacius
Partner
Partner

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
Master III
Master III

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

senpradip007
Specialist III
Specialist III

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

];