Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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.

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: help me in triming the values?

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

View solution in original post

11 Replies
Highlighted

Re: help me in triming the values?

Hi,

use Subfield()

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

Regards

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

Re: help me in triming the values?

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

Highlighted
Master II
Master II

Re: help me in triming the values?

Hi,

Try this:

Load

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

HTH

Sushil

Highlighted
Specialist
Specialist

Re: help me in triming the values?

Hi!

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

Highlighted
Master III
Master III

Re: help me in triming the values?

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

Highlighted
Creator III
Creator III

Re: help me in triming the values?

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

pls explain.

Highlighted

Re: help me in triming the values?

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

View solution in original post

Highlighted
Specialist
Specialist

Re: help me in triming the values?

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 ';'

Highlighted
Master III
Master III

Re: help me in triming the values?

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