topic Extract number from values in a field in QlikView

Extract number from values in a field

Anonymous — Mon, 22 Feb 2016 15:31:12 GMT

Hi,

I have a field that has Alpha Numeric strings as data.

Example:

Field: OBJ ID

ABCD-45284-DKFNG

AB-373-FHFTU

FDKJJGJ-4688450-ABC

............

I need to extract only numbers from the data and show it in a table/chart.

Note: The numbers in all the rows are not of equal length. Also, the text in before and after the number values aren't the same and are of equal length.

Re: Extract number from values in a field

sunny_talwar — Mon, 22 Feb 2016 16:53:21 GMT

May be do this in the load script:

KeepChar([OBJ ID], '1234567890') as NumberField

Sample Script:

Table:

LOAD *,

KeepChar([OBJ ID], '1234567890') as NumberField;

LOAD * Inline [

OBJ ID

ABCD-45284-DKFNG

AB-373-FHFTU

FDKJJGJ-4688450-ABC

];

Re: Extract number from values in a field

maxgro — Mon, 22 Feb 2016 19:19:24 GMT

or this if the number is always between "-"

Load

[OBJ ID],

SubField([OBJ ID], '-', 2) as [NEW OBJ ID];

LOAD * Inline [

OBJ ID

ABCD-45284-DKFNG

AB-373-FHFTU

FDKJJGJ-4688450-ABC

];

Re: Extract number from values in a field

fonmarcelo — Tue, 23 Feb 2016 13:34:42 GMT

Do it:

Test:

LOAD

SubField([OBJ ID],'-',2) as [OBJ NUMBER];

LOAD * Inline [

'OBJ ID'

ABCD-45284-DKFNG

AB-373-FHFTU

FDKJJGJ-4688450-ABC

];

Re: Extract number from values in a field

Anonymous — Tue, 23 Feb 2016 14:41:38 GMT

Thanks guys. Will try out and let you know.