topic Re: Extract a six digit number from a string that also contains other numbers in QlikView

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

canoebi99 — Tue, 17 Nov 2015 00:41:37 GMT

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

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

Mark_Little — Tue, 17 Nov 2015 09:05:15 GMT

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

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

canoebi99 — Tue, 17 Nov 2015 09:22:35 GMT

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.

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

Anonymous — Tue, 17 Nov 2015 09:35:52 GMT

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?

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

Kushal_Chawda — Tue, 17 Nov 2015 09:40:50 GMT

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;

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

Kushal_Chawda — Tue, 17 Nov 2015 11:07:38 GMT

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

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

pljsoftware — Wed, 18 Nov 2015 13:27:13 GMT

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

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

Gysbert_Wassenaar — Wed, 18 Nov 2015 14:23:51 GMT

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 
];