topic Re: which function I can use? in QlikView

which function I can use?

Sun, 27 Sep 2015 06:29:45 GMT

Hello All

I have a field with values like below

1234-abc

13422-monday

136647536-tuesday

I need to display only the part before '-'

eg) 1234 , 13422, 136647536.

Kindly help me what function I can use to get above result.

Thanks in advance...

Re: which function I can use?

sunny_talwar — Sun, 27 Sep 2015 15:56:05 GMT

You can use SubField function here:

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

Re: which function I can use?

Peter_Cammaert — Sun, 27 Sep 2015 15:57:29 GMT

=SubField(YourOriginalField, '-', 1)

Re: which function I can use?

oknotsen — Sun, 27 Sep 2015 15:59:07 GMT

= left(fieldname, (len(fieldname) - index(fieldname, '-'))

Re: which function I can use?

sunny_talwar — Sun, 27 Sep 2015 15:59:20 GMT

See this script:

Table:

LOAD *,

SubField(FieldName, '-', 1) as Part1,

SubField(FieldName, '-', 2) as Part2;

LOAD * Inline [

FieldName

1234-abc

13422-monday

136647536-tuesday

];

It's output:

Re: which function I can use?

chrisg — Sun, 27 Sep 2015 16:07:15 GMT

Hi Chintan,

try this:

left(Value,index(Value,'-')-1) AS Partleft

best regards

Christoph

Re: which function I can use?

Anonymous — Mon, 28 Sep 2015 04:34:24 GMT

Hello Sunny

Can I use this in front end report ? I want something which can be user in report and not script.

Regards

Chintan Gala

Re: which function I can use?

avinashelite — Mon, 28 Sep 2015 05:01:39 GMT

Yes you can use in the front end also .

subfield(filed_name,'-',1) use this expression in the list box and charts

Re: which function I can use?

Anonymous — Mon, 28 Sep 2015 05:02:34 GMT

Thanks!

Re: which function I can use?

Anonymous — Mon, 28 Sep 2015 05:16:19 GMT

Good discussion thread here. Please be sure to mark helpful and correct answers.

Sara

Re: which function I can use?

nagarjuna_kotha — Mon, 28 Sep 2015 05:27:52 GMT

Yes,U can use it in front end also.

Re: which function I can use?

nagarjuna_kotha — Mon, 28 Sep 2015 05:30:04 GMT

better way is create in script level to use further .

Hope this help

Re: which function I can use?

ChennaiahNallani — Mon, 28 Sep 2015 05:39:34 GMT

Hi,

Use below script.

Tab:

LOAD * Inline [

FiledName

1234-abc

13422-monday

136647536-tuesday];

Tab1:

LOAD

KeepChar(FiledName,'0123456789') as FiledName1

Resident Tab;

Re: which function I can use?

sasiparupudi1 — Mon, 28 Sep 2015 05:49:12 GMT

use Keepchar in your script

KeepChar(YourFiled,'0123456789') as YourFiled

Re: which function I can use?

Anonymous — Mon, 28 Sep 2015 05:50:30 GMT

Where exactly in front end?? Please helo..

Re: which function I can use?

nizamsha — Mon, 28 Sep 2015 06:01:01 GMT

U can Use this if its suits ur scenario

=KeepChar('1234-abc',0123456789)

=SubField('1234-abc','-',1)

Re: which function I can use?

Anonymous — Mon, 28 Sep 2015 06:06:00 GMT

You mentioned to use in expression. But the field is a dimension.

Re: which function I can use?

Anonymous — Mon, 28 Sep 2015 06:12:00 GMT

Yes. Give me some time. I am trying the sokution. Will inform once it is sccessful..

Re: which function I can use?

Anonymous — Mon, 28 Sep 2015 06:47:04 GMT

Hello Sunny

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

Above is not working for me in the script.

Re: which function I can use?

Mon, 28 Sep 2015 07:06:08 GMT

You can use the funciton SubField, check the attached document to see how it works.

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