Find Numbers from the the string

[ashishpalkar]

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,

[Clever_Anjos]

Maybe

KeepChar(yourfield,'0123456789 ')

can solve your need

[sunny_talwar]

May be this:

KeepChar(FieldName, '0123456789') as Number

[ashishpalkar]

KeepChar(yourfield,'0123456789 ')

this is concatenating all values,

0858059089459789

We are looking output like following

033355 088555 055777

[maxgro]

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 '|');

[sinanozdemir]

Hi,

This should do it:

I am also attaching the qvw.

Hope this helps.

Other useful links by Sinan:

Handling Metadata in QlikView

QlikView and Python Integration

QlikView Automation Series – Unzip & Load Excel Files:

[ashishpalkar]

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?

[sinanozdemir · Accepted solution]

This might work:

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

[ashishpalkar]

Thanks all for your suggestions..