topic Trimming a Field using an Expression in QlikView

Trimming a Field using an Expression

Anonymous — Wed, 14 Oct 2015 12:36:53 GMT

Hi Guys,

I am using the below expression to return a Descriptive line from a bill:

Only({< DESCR_ON_BILL={"Sing*"}, ACCT_ID={123456789A}>}DESCR_ON_BILL)

Which returns the full field as below

But I just want the expression to return the 0.14 value. However, I am not sure how to use the trim function within my current expression?

I cant change my load script as I need the full field in another part of the report so it would have to be through the expression.

Any help would be great,

cheers

Re: Trimming a Field using an Expression

swuehl — Wed, 14 Oct 2015 12:38:20 GMT

You can use subfield to extract the value:

=SubField(Only({< DESCR_ON_BILL={"Sing*"}, ACCT_ID={123456789A}>}DESCR_ON_BILL) ,'¬',-2)

Re: Trimming a Field using an Expression

avinashelite — Wed, 14 Oct 2015 12:39:56 GMT

if the position of the dollar values i.e. 0.14 is fixed , than you cant use the sub field function with the offset value

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: Trimming a Field using an Expression

swuehl — Wed, 14 Oct 2015 12:41:59 GMT

Another possible solution:

=Textbetween( Only({< DESCR_ON_BILL={"Sing*"}, ACCT_ID={123456789A}>}DESCR_ON_BILL) ,'€','¬')

Re: Trimming a Field using an Expression

Anonymous — Wed, 14 Oct 2015 12:48:48 GMT

This seems to work perfectly. Thank you