Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
durgabhavani
Creator III
Creator III

help me in triming the values?

I need to remove the field value SYST: in the information field.

Information
SYST: ACCOUNTS
SYST: ACCOUNTS
SYST: ACCOUNTS
SYST: ACCOUNTS
SYST: ACCOUNTS
SYST: ACCOUNTS
SYST: FINANCE
SYST: FINANCE
SYST: FINANCE
SYST: FINANCE
SYST: FINANCE

help me in trimming.

1 Solution

Accepted Solutions
MayilVahanan

Hi

Try like this

=SubField('SYST: ACCOUNTS',':',-1)

Trim(SubField(FieldName,':',-1)) as FieldName


Here -1 represents the last value after the ':'


=SubField('SYST: ACCOUNTS',':',2)

Here 2 represents the second value after ":" delimiter


Ex:

SubField('SYST: ACCOUNTS: 3',':',2) gives ACCOUNTS

SubField('SYST: ACCOUNTS: 3',':',-1) gives 3

Thanks & Regards,
Mayil Vahanan R

View solution in original post

11 Replies
PrashantSangle

Hi,

use Subfield()

try like subfield(fieldName,': ',2)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
antoniotiman
Master III
Master III

Replace(Information,'SYST:','')

sushil353
Master II
Master II

Hi,

Try this:

Load

Replace(Information,'SYST:','') as Information

HTH

Sushil

pokassov
Specialist
Specialist

Hi!

=Mid(Information,7,len(Information))

sasiparupudi1
Master III
Master III

trim(subfield(YourField,':',2))

durgabhavani
Creator III
Creator III
Author

Thanks for reply! why you used the 2 in function.

pls explain.

MayilVahanan

Hi

Try like this

=SubField('SYST: ACCOUNTS',':',-1)

Trim(SubField(FieldName,':',-1)) as FieldName


Here -1 represents the last value after the ':'


=SubField('SYST: ACCOUNTS',':',2)

Here 2 represents the second value after ":" delimiter


Ex:

SubField('SYST: ACCOUNTS: 3',':',2) gives ACCOUNTS

SubField('SYST: ACCOUNTS: 3',':',-1) gives 3

Thanks & Regards,
Mayil Vahanan R

View solution in original post

qlikmsg4u
Specialist
Specialist

See the Help

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

Examples:

(For three parameters)


subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

subfield(S, ';' ,1) returns NULL if S is an empty string

subfield(S, ';' ,1) returns an empty string if S is ';'

sasiparupudi1
Master III
Master III

Subfield can return parts of the string based on a given delimeter. In your case it is :

Piece 1 =SYST

delimeter= :

Piece 2 = ACCOUNTS


So we are simply telling the subfield function that we need the piece 2 of the string


HTH