topic remove prefix values in List Box in QlikView

remove prefix values in List Box

Srinivas — Thu, 19 Jun 2014 05:15:55 GMT

Hi,

I have one list box in that remove prefix values with dynamically. see blow example,

20-3214

20-8765

200-3217

2000-8775

30-1365

60-2348

PPSE+10-7654

PPSE+20-8765

in that list box prefix have some times have Two,Three and Four times. This is problem i can't able to remove those prefix values.

Please help on this..

Regards & Thanks

Munna

Re: remove prefix values in List Box

manojkulkarni — Thu, 19 Jun 2014 05:24:54 GMT

Didn't get what exactly is ur requirement ? can elaborate bit.

Didn't understand "list box prefix have some times have Two,Three and Four times" what is this mean

Re: remove prefix values in List Box

selvakumarsr — Thu, 19 Jun 2014 05:27:20 GMT

Then why cant you take only the last 4 characters..

Right(ColumnName,4)

Selva

Re: remove prefix values in List Box

Srinivas — Thu, 19 Jun 2014 05:31:36 GMT

Hi Selva,

i want remove before '-' Whatever values i have... like 20- ,200-,30-, 2000- so and... this values want to remove.

20-32145

20-8765

200-3217

2000-877587

30-136523

60-2348

PPSE+10-7654997

PPSE+20-876534567

Regards

Munna

Re: remove prefix values in List Box

Srinivas — Thu, 19 Jun 2014 05:38:10 GMT

Hi Manoj,

I want remove before '-' Whatever values i have... like 20- ,200-,30-, 2000- so and... this values want to remove.

20-32145

20-8765

200-3217

2000-877587

30-136523

60-2348

PPSE+10-7654997

PPSE+20-876534567

Regards

Munna

Re: remove prefix values in List Box

sujeetsingh — Thu, 19 Jun 2014 05:42:05 GMT

Use Subfield () string function

Re: remove prefix values in List Box

rohit214 — Thu, 19 Jun 2014 05:42:31 GMT

try this

subfield(Fieldname, '-' ,2)

Thanks

Rohit

Re: remove prefix values in List Box

selvakumarsr — Thu, 19 Jun 2014 05:45:35 GMT

Try this example,

Phone:
NoConcatenate LOAD
ID,
Phone,
Left(Phone, Index(Phone, '-')-1) as Phone1,
Mid(Phone, Index(Phone, '-')+1, 3) as Phone2,
Right(Phone, Len(Phone)-Index(Phone, '-', 2)) as Phone3
Resident PhoneTemp;

Re: remove prefix values in List Box

sujeetsingh — Thu, 19 Jun 2014 05:45:45 GMT

See this sample

Re: remove prefix values in List Box

manojkulkarni — Thu, 19 Jun 2014 05:47:56 GMT

mid(urText,Index(urText,'-')+1)

Re: remove prefix values in List Box

ashfaq_haseeb — Thu, 19 Jun 2014 05:52:45 GMT

Hi,

Try below.

Load *,subfield(Value, '-' ,2) as NewValue;

load * inline

[

Value

20-3214

20-8765

200-3217

2000-8775

30-1365

60-2348

PPSE+10-7654

PPSE+20-8765

];

Regards

ASHFAQ

Re: remove prefix values in List Box

Thu, 19 Jun 2014 05:56:37 GMT

Hi,

Try the TextBetween() Function.

For example. TextBetween('PPSE+10-7654997','-','')

Result : 7654997.

Regards.

Re: remove prefix values in List Box

Srinivas — Wed, 27 Aug 2014 05:52:02 GMT

Hi Sujeet,

As for ur logic removed before '-' values it's working fine, But here my question is some values have without '-' so in that situation not working properly could u please check.

20-32145

11745

289634

467912

20-8765

200-3217

2000-877587

30-136523

60-2348

PPSE+10-7654997

PPSE+20-876534567

Regards,

Munna

Re: remove prefix values in List Box

Srinivas — Wed, 27 Aug 2014 05:54:06 GMT

Thanks Ashfaq,

As for ur logic removed before '-' values it's working fine, But here my question is some values have without '-' so in that situation not working properly this list box showed only '-' values not other,Could u please check.

20-32145

11745

289634

467912

20-8765

200-3217

2000-877587

30-136523

60-2348

PPSE+10-7654997

PPSE+20-876534567

Regards,

Munna

Re: remove prefix values in List Box

ashfaq_haseeb — Wed, 27 Aug 2014 05:55:49 GMT

Hi,

Post sample data.

and try to close thready once its answered your concern.

Regards

ASHFAQ

Re: remove prefix values in List Box

ashfaq_haseeb — Wed, 27 Aug 2014 06:01:50 GMT

Hi,

Try below code.

Load *,if(num(Value),Value,subfield(Value, '-' ,2)) as NewValue;

load * inline

[

Value

20-32145

11745

289634

467912

20-8765

200-3217

2000-877587

30-136523

60-2348

PPSE+10-7654997

PPSE+20-876534567

];

Regards

ASHFAQ

Re: remove prefix values in List Box

ashfaq_haseeb — Wed, 27 Aug 2014 06:02:24 GMT

Now close this thread by selecting appropriate answer.

Regards

ASHFAQ

Re: remove prefix values in List Box

Srinivas — Wed, 27 Aug 2014 06:15:33 GMT

Hi Ashfaq,

See below sample data.

ID dimension contains 12 values & after applying subfield function then it's NewID dimension contain 7 values only means here NewID dimension doesn't take without '-' values.But i need all 12 values in NewID dimension.

Load *,subfield(ID, '-' ,2) as NewID;

load * inline

[

20-3214

278634

387634

987643

765247

20-8765

200-3217

2000-8775

30-1365

60-2348

PPSE+10-7654

PPSE+20-8765

];

Re: remove prefix values in List Box

Srinivas — Wed, 27 Aug 2014 06:17:55 GMT

Thanks a lot Ashfaq, It's working fine.

Re: remove prefix values in List Box

ashfaq_haseeb — Wed, 27 Aug 2014 06:20:38 GMT

Hi did you try my code

Load *,if(num(Value),Value,subfield(Value, '-' ,2)) as NewValue;

load * inline

[

Value

20-32145

11745

289634

467912

20-8765

200-3217

2000-877587

30-136523

60-2348

PPSE+10-7654997

PPSE+20-876534567

];

Regards

ASHFAQ