Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
This might work:
It is pretty much what maxgro did, but in the front end.
Maybe
KeepChar(yourfield,'0123456789 ')
can solve your need
May be this:
KeepChar(FieldName, '0123456789') as Number
KeepChar(yourfield,'0123456789 ')
this is concatenating all values,
0858059089459789
We are looking output like following
033355 088555 055777
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 '|');
Hi,
This should do it:
I am also attaching the qvw.
Hope this helps.
Other useful links by Sinan:
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?
This might work:
It is pretty much what maxgro did, but in the front end.
Thanks all for your suggestions..