Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rakeshpaul
New Contributor

Sub-string

Hi All,

I am trying to extract the text's before the open braces. How can I do that. Here I am pasting my data points, please help me out how I can do that.

                       

GUJRAT
MUMBAI (2 to 2.5 Kg)
NASIK (2 to 2.5 Kg)
PUNE (2 to 2.5 Kg)
PUNJAB (1.8 to 2 Kg)
PUNJAB (1.4 to 1.6 Kg)
PUNJAB (1 Kg)
SKYLARK HR (2 to 2.3 Kg)
SKYLARK HR (1.6 to 1.7 Kg)
SKYLARK HR (1.2 to 1.3 Kg)
RAIPUR (1.8 to 2.5 kg)
RAIPUR (1.1 to 1.3 kg)

Regards,

Rakesh Paul

Tags (2)
5 Replies
ecolomer
Honored Contributor II

Re: Sub-string

With Subfield function:

Data:

LOAD GUJRAT

FROM

[http://community.qlik.com/thread/148004]

(html, codepage is 1252, embedded labels, table is @1);

LOAD

  GUJRAT,

  SubField(GUJRAT, '(', 1) as Previo

resident Data;

DROP TABLE Data;

GUJRAT Previo
MUMBAI (2 to 2.5 Kg)MUMBAI
NASIK (2 to 2.5 Kg)NASIK
PUNE (2 to 2.5 Kg)PUNE
PUNJAB (1 Kg)PUNJAB
PUNJAB (1.4 to 1.6 Kg)PUNJAB
PUNJAB (1.8 to 2 Kg)PUNJAB
RAIPUR (1.1 to 1.3 kg)RAIPUR
RAIPUR (1.8 to 2.5 kg)RAIPUR
SKYLARK HR (1.2 to 1.3 Kg)SKYLARK HR
SKYLARK HR (1.6 to 1.7 Kg)SKYLARK HR
SKYLARK HR (2 to 2.3 Kg)SKYLARK HR
chauhans85
Esteemed Contributor

Re: Sub-string

if(findoneof(Fieldname,'('),subfield(fieldname,' ',1),fieldname)

hope this helps

MVP
MVP

Re: Sub-string

another option, using left and index functions (in script or in chart, image is for chart)

left(GUJRAT, index(GUJRAT, '(')-1)

1.png

Gabriel
Valued Contributor II

Re: Sub-string

Hi,

Try this for both part 1 and part 2 extract

Tabe1:

LOAD GUJRAT

FROM

[http://community.qlik.com/thread/148004]

(html, codepage is 1252, embedded labels, table is @1);

TABLE2:

LOAD

*,

SubField(GUJRAT,'(',1) AS Extract_Part_1

,'('& SubField(GUJRAT,'(',2)    AS Extract_Part_2

Resident Tabe1

;

Hope this helps

Re: Sub-string

Hi,

also possible:

QlikCommunity_Thread_148004_Pic1.JPG

LOAD TextBetween('@'&GUJRAT,'@','(') as GUJRAT

FROM [http://community.qlik.com/thread/148004] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Community Browser