topic Sub-string in QlikView

Sub-string

rakeshpaul — Sun, 11 Jan 2015 08:16:05 GMT

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

Re: Sub-string

ecolomer — Sun, 11 Jan 2015 08:29:20 GMT

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

Re: Sub-string

SunilChauhan — Sun, 11 Jan 2015 14:59:29 GMT

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

hope this helps

Re: Sub-string

maxgro — Sun, 11 Jan 2015 15:38:50 GMT

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

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

Re: Sub-string

Gabriel — Sun, 11 Jan 2015 22:26:35 GMT

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

MarcoWedel — Mon, 12 Jan 2015 14:53:42 GMT

Hi,

also possible:

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