topic Re: Remove characters in QlikView

Remove characters

gautik92 — Wed, 25 Nov 2020 16:16:04 GMT

I have a field containing records lik this

10000-ABC

ABC123

123NBA

I want to remove all alphabets and show olny nos

Expected Output

10000

123

Re: Remove characters

MK_QSL — Mon, 26 Dec 2016 11:11:23 GMT

KeepChar(ID,'0123456789') as ID

Re: Remove characters

gautik92 — Mon, 26 Dec 2016 11:23:00 GMT

i tried it already but not working

Re: Remove characters

Chanty4u — Mon, 26 Dec 2016 11:26:14 GMT

Hi,

Adding to Manish,

One more solution

LOAD * INLINE [

10000-ABC

ABC123

123NBA

];

Result:

LOAD *,

PurgeChar(ID,'ABCDEFGHIJKLMNOPQRSTUVWXYX-') AS NEW

Resident a;

Re: Remove characters

MK_QSL — Mon, 26 Dec 2016 11:28:21 GMT

check this..

Re: Remove characters

MK_QSL — Mon, 26 Dec 2016 11:29:33 GMT

In case we have other special characters apart from - we need to add them in PurgeChar which is not a good idea.

Re: Remove characters

Chanty4u — Mon, 26 Dec 2016 11:30:53 GMT

yeah manish you are right . but instance he can use this for this case

Re: Remove characters

gautik92 — Mon, 26 Dec 2016 11:56:15 GMT

but what if i Have a record lik this

123-abc123

i want only 123

Re: Remove characters

MK_QSL — Mon, 26 Dec 2016 11:59:38 GMT

You mean 1st 123?

What if you have

234-DEF-222

What is the result you are looking for?

Re: Remove characters

Chanty4u — Mon, 26 Dec 2016 12:06:22 GMT

use subfield function for this

From Help file

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: Remove characters

gautik92 — Mon, 26 Dec 2016 12:16:24 GMT

I want only 234

Re: Remove characters

MK_QSL — Mon, 26 Dec 2016 12:24:53 GMT

It would be better, if you provide few more sample ID along with expected output.

10000-ABC 10000

ABC123 123

123NBA 123

123-abc123 123

234-DEF-222 234

ABC-10000 ?

234DEF222 ?

DEF234-222 ?

Provide whatever different criteria of ID you are having.

Or, provide us the proper logic to work..

Re: Remove characters

varshavig12 — Tue, 27 Dec 2016 05:18:25 GMT

Is this okay ?

Re: Remove characters

Anonymous — Tue, 27 Dec 2016 09:17:08 GMT

Hi,

Use below script to archive desired result.

Table1:

LOAD * INLINE [

10000-ABC

ABC123

123NBA

];

Table2:

Load

PurgeChar(ID,'ABCDEFGHIJKLMNOPQRSTUVWXYX-') as NEWID

Resident Table1;

Drop Table Table1;

Warm regards,

Venkata Sreekanth