Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
canoebi99
Partner - Creator
Partner - Creator

Extract a six digit number from a string that also contains other numbers

Hi All,

I need some assistance from some experts out there!

I have a string field that contains a job number and I need to extract the job number.

Unfortunately there are other numbers in the string which complicates things.

Example strings that I am working with are below:

Microsoft Word - 316680_15196160415_CQP Stage 3_bes               - I need to extract 316680 from this string

12275 151028 Stage 1 2 Post Construction Audit                              - I need to extract 151028 from this string


So basically any 6 digit string needs to be extracted.


Does anyone have any ideas as to how I can do this (in the script)?


Many thanks

Ben

7 Replies
Mark_Little
Luminary
Luminary

Hi,

Would they always be like the above?

As you could replace space with _ so they are formatted the same and then use subfield

Something like SUBFIELD(REPLACE(Field,' ','_'),'_',2) as Extracted?

Mark

canoebi99
Partner - Creator
Partner - Creator
Author

Thanks mark, but unfortunately no.

There are all kinds of combinations of text and numbers.

I need someway of only extracting those numbers found in the string that are exactly 6 numbers long regardless of what is either side of them. (space, underscore etc)

I think I may be getting close with a regex function that I have found but I am still playing with it.

I will post back if I get it to work.

Anonymous
Not applicable

Here concern is what, you dont have fix format of string.

Some where you are having '_', ' ' and may be some other special character.

Could you post some sample data?

Kushal_Chawda

try something like this,

Data:

LOAD *, SubField(Replace(Field,' ','_'),'_') as Output Inline [

Field

316680_15196160415_CQP Stage 3_bes              

12275 151028 Stage 1 2 Post Construction Audit ];

Inner Join

LOAD Distinct Data,

Output

Resident Data

where len(Field)=6;

Kushal_Chawda

what if there is two numbers with length six? fro eg. 12 12345 abc 456788

pljsoftware
Creator III
Creator III

Hi Ben,

this is the code

t0:

LOAD

  Field

  ,SubField(value, '_') as value

;

LOAD

  *

  ,SubField(Field, ' ') as value

;

LOAD * Inline [

Field

Microsoft Word - 316680_15196160415_CQP Stage 3_bes

12275 151028 Stage 1 2 Post Construction Audit

12275 000028 Stage 1 2 Post Construction Audit

];

t1:

LOAD

  Field

  ,value as OnlyNum

Resident t0

Where IsNum(KeepChar(value, '0123456789')) and Len(KeepChar(value, '0123456789')) = 6;

I have added a you row with 000028 to test the result of my where condition and is all ok.

Best Regards

Luca Jonathan Panetta

Gysbert_Wassenaar

This will do the trick;

LOAD *, KeepChar(Mid(SubString,2,6),'0123456789') as Number

WHERE

  not IsNum(Left(SubString,1))

  and not IsNum(Right(SubString,1))

  and KeepChar(Mid(SubString,2,6),'0123456789') = Mid(SubString,2,6);

LOAD *, Text(Mid(TestString,IterNo(),8)) as SubString, IterNo() as ID

While IterNo() <= Length-8;

LOAD *,'@' & String & '@' as TestString, Len(String)+2 as Length INLINE [

String

Microsoft Word - 316680_15196160415_CQP Stage 3_bes

12275 151028 Stage 1 2 Post Construction Audit

];


talk is cheap, supply exceeds demand