topic Re: Find Numbers from the the string in QlikView

Find Numbers from the the string

ashishpalkar — Wed, 24 Feb 2016 18:51:11 GMT

Hi Experts

I am getting data from Database in following format.

1) 33355-CLIENT DATA ONLY - DO NOT USE,88555-PCI EXEC,55777-NA CLIENT OVERALL DATA

2) 11111-UTILITIES,22222-CLIENT OVERALL DATA BUSINESS SUPPORT & ANALYSIS

3) 07532-DATA ANALYSIS,88555-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,88899-NA CREDIT DERIV INVESTMENTS

4) 33222- NEW YORK-10101,20202-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,66555-NA CREDIT DERIV INVESTMENTS

we would like to display only numbers from the above strings like this,

033355 088555 055777

011111 022222 00000

007532 088555 088899

033222 010101 020202 066555

Thanks in advance,

Re: Find Numbers from the the string

Clever_Anjos — Wed, 24 Feb 2016 18:54:38 GMT

Maybe

KeepChar(yourfield,'0123456789 ')

can solve your need

Re: Find Numbers from the the string

sunny_talwar — Wed, 24 Feb 2016 18:56:12 GMT

May be this:

KeepChar(FieldName, '0123456789') as Number

Re: Find Numbers from the the string

ashishpalkar — Wed, 24 Feb 2016 19:05:23 GMT

KeepChar(yourfield,'0123456789 ')

this is concatenating all values,

0858059089459789

We are looking output like following

033355 088555 055777

Re: Find Numbers from the the string

maxgro — Wed, 24 Feb 2016 19:44:31 GMT

load Concat(newfield, ' ', id2) as newfield, id

group by id;

load

num(subfield(newfield, ' '), '000000') as newfield,

recno() as id2,

id;

load

recno() as id,

field,

trim(

replace(replace(replace(

replace(KeepChar(field, '0123456789-,'), ',', '-'),

'---', ' '), '--', ' '), '-', ' ')

)

as newfield inline [

field

33355-CLIENT DATA ONLY - DO NOT USE,88555-PCI EXEC,55777-NA CLIENT OVERALL DATA

11111-UTILITIES,22222-CLIENT OVERALL DATA BUSINESS SUPPORT & ANALYSIS

07532-DATA ANALYSIS,88555-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,88899-NA CREDIT DERIV INVESTMENTS

33222- NEW YORK-10101,20202-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,66555-NA CREDIT DERIV INVESTMENTS

] (delimiter is '|');

EDIT

added a load for sorting

load Concat(newfield, ' ', id2) as newfield, id group by id;

load *, recno() as id2;

load num(subfield(newfield, ' '), '000000') as newfield, id;

load recno() as id, field,

trim(

replace(replace(replace(

replace(KeepChar(field, '0123456789-,'), ',', '-'),

'---', ' '), '--', ' '), '-', ' ')

)

as newfield inline [

field

33355-CLIENT DATA ONLY - DO NOT USE,88555-PCI EXEC,55777-NA CLIENT OVERALL DATA

11111-UTILITIES,22222-CLIENT OVERALL DATA BUSINESS SUPPORT & ANALYSIS

07532-DATA ANALYSIS,88555-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,88899-NA CREDIT DERIV INVESTMENTS

33222- NEW YORK-10101,20202-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,66555-NA CREDIT DERIV INVESTMENTS

22233-CCB - NEW -0234,02233-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,88555-NA CREDIT DERIV

] (delimiter is '|');

Re: Find Numbers from the the string

sinanozdemir — Wed, 24 Feb 2016 20:12:33 GMT

Hi,

This should do it:

I am also attaching the qvw.

Hope this helps.

Re: Find Numbers from the the string

ashishpalkar — Wed, 24 Feb 2016 20:27:16 GMT

Thanks Sinan

your solution works well , however for below data set its not working.

22233-CCB - NEW -0234,02233-CLIENT REPORTING REFERENCE ONLY - DO NOT USE,88555-NA CREDIT DERIV

any suggestions?

Re: Find Numbers from the the string

sinanozdemir — Wed, 24 Feb 2016 21:11:15 GMT

This might work:

It is pretty much what maxgro‌ did, but in the front end.

Re: Find Numbers from the the string

ashishpalkar — Wed, 24 Feb 2016 21:17:10 GMT

Thanks all for your suggestions..