topic Re: help me in triming the values? in QlikView

help me in triming the values?

durgabhavani — Wed, 22 Jul 2015 07:55:16 GMT

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

Information

SYST: ACCOUNTS

SYST: FINANCE

help me in trimming.

Re: help me in triming the values?

PrashantSangle — Wed, 22 Jul 2015 07:57:36 GMT

Hi,

use Subfield()

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

Regards

Re: help me in triming the values?

antoniotiman — Wed, 22 Jul 2015 07:58:35 GMT

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

Re: help me in triming the values?

sushil353 — Wed, 22 Jul 2015 07:58:41 GMT

Hi,

Try this:

Load

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

HTH

Sushil

Re: help me in triming the values?

pokassov — Wed, 22 Jul 2015 07:58:58 GMT

Hi!

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

Re: help me in triming the values?

sasiparupudi1 — Wed, 22 Jul 2015 08:06:55 GMT

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

Re: help me in triming the values?

durgabhavani — Wed, 22 Jul 2015 08:14:51 GMT

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

pls explain.

Re: help me in triming the values?

MayilVahanan — Wed, 22 Jul 2015 08:21:49 GMT

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

Re: help me in triming the values?

qlikmsg4u — Wed, 22 Jul 2015 08:21:53 GMT

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

Re: help me in triming the values?

sasiparupudi1 — Wed, 22 Jul 2015 08:22:29 GMT

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

Re: help me in triming the values?

jagan — Wed, 22 Jul 2015 09:39:33 GMT

Hi Durga,

Simply use the Replace() like below in script

TableName:

Load

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

From DataSource;

Hope this helps you.

Regards,

Jagan.

Re: help me in triming the values?

durgabhavani — Wed, 22 Jul 2015 10:11:48 GMT

Thanks for everyone!