topic Re: Extract 6-digit number string from text fied in QlikView

Extract 6-digit number string from text fied

Wed, 30 Mar 2016 15:05:53 GMT

Dear all,

I have a text field covering various information, such as cost center number, creditor, number, date number and text information.

I want to extract the creditor information. The issue I have is, that the Position of the creditor number within the text field is not consistent and that I have multiple information with numbers. Thus keepchar formula or left/right funktion I tried didn't work properly.

The critieria that would be unique are, that the creditor number has always 6 Digits and can only be between 700000 and 899999.

Can you think of combination of QV-functions that would cover above requirements?

Thanks and best regards,

Tobias

Re: Extract 6-digit number string from text fied

swuehl — Wed, 30 Mar 2016 15:12:34 GMT

You probably can, but I would also suggest looking into using regular expressions, maybe start with

How to use regular expressions

Re: Extract 6-digit number string from text fied

sunny_talwar — Wed, 30 Mar 2016 15:12:37 GMT

Can you share few possibilities from where we are trying to extract this?

Re: Extract 6-digit number string from text fied

Kushal_Chawda — Wed, 30 Mar 2016 15:13:53 GMT

Can you post 2 or 4 sample values to build solution?

Re: Extract 6-digit number string from text fied

swuehl — Wed, 30 Mar 2016 15:16:56 GMT

Another option,create a mapping table for your values you are searching, then use MapSubstring() and Textbetween to filter your table for matches, like shown here:

Re: Find first repeated word in a text

Re: Extract 6-digit number string from text fied

Wed, 30 Mar 2016 15:23:54 GMT

Maybe this screenshot is helpful. From the first line I Need to extract the number '822201'. From the second line the '822561'. From the third line I need the number '808386'.

Re: Extract 6-digit number string from text fied

Kushal_Chawda — Wed, 30 Mar 2016 15:24:28 GMT

for now you can try something like this

Data:
LOAD *, if(len(keepchar(TextBetween(Data,'700000','899999'),'0123456789'))=6,
keepchar(TextBetween(Data,'700000','899999'),'0123456789'),'') as Creditor
Inline [
Data
abc-700000-012345-899999-xyz
abc-700000-01234522-899999-xyz ];

Re: Extract 6-digit number string from text fied

sunny_talwar — Wed, 30 Mar 2016 15:28:16 GMT

I may not be right, but I think by in-between 700000 and 899999 he means that the 6 digit number ranges between those two numbers

Re: Extract 6-digit number string from text fied

Kushal_Chawda — Wed, 30 Mar 2016 15:36:49 GMT

Yes, may be u r rite, but then we can't do any thing till we not get the sample values

Re: Extract 6-digit number string from text fied

Wed, 30 Mar 2016 15:38:33 GMT

Sunny T, you are right. The 6 Digit number ranges between 700000 and 899999.

In a table the entire list of possible creditor numbers would be 700001,700002,...,899998 and 899999

Re: Extract 6-digit number string from text fied

Kushal_Chawda — Wed, 30 Mar 2016 15:43:15 GMT

But, can you show us some sample values, i mean entire string value?

Re: Extract 6-digit number string from text fied

swuehl — Wed, 30 Mar 2016 15:43:34 GMT

So what about

MAP:

MAPPING

LOAD

699999+recno() as F1,

'\/' & num(699999+recno(),'000000') & '/\' as F2

AutoGenerate 200000;

Table:

LOAD Records,

TextBetween(MapSubString('MAP',Records), '\/','/\',1) as Number;

LOAD * Inline [

Records

something-123456-Hotman-something-something

something-Hotman-701245-something-XMan

];

Re: Extract 6-digit number string from text fied

sunny_talwar — Wed, 30 Mar 2016 15:44:08 GMT

He did provide a sample here -> Re: Extract 6-digit number string from text fied

Re: Extract 6-digit number string from text fied

sunny_talwar — Wed, 30 Mar 2016 15:45:22 GMT

Amazing Stefan.

Worked for his sample

MAP:

MAPPING

LOAD

699999+recno() as F1,

'\/' & num(699999+recno(),'000000') & '/\' as F2

AutoGenerate 200000;

Table:

LOAD *,

TextBetween(MapSubString('MAP', FieldName), '\/','/\',1) as Number;

LOAD * Inline [

FieldName

822201 Aufl TP Vision Ambilight

822561 Aufl LG Electro Aktion

Passivierung zum 31.12.2015 808386 M1311420

Passivierung zum 31.12.2015 747150 M2651300

];

Re: Extract 6-digit number string from text fied

Wed, 30 Mar 2016 15:53:00 GMT

Thanks a lot. This is great

Re: Extract 6-digit number string from text fied

Kushal_Chawda — Wed, 30 Mar 2016 15:56:27 GMT

ek number swuehl‌

Re: Extract 6-digit number string from text fied

sunny_talwar — Wed, 30 Mar 2016 15:58:18 GMT

Hindi mein Number 1? Kush

Re: Extract 6-digit number string from text fied

Kushal_Chawda — Wed, 30 Mar 2016 16:36:42 GMT

hahaha Yeah sunindia‌

Re: Extract 6-digit number string from text fied

Janpeer — Wed, 19 Jan 2022 23:49:29 GMT

This is a great solution and for 6 digits, it works fine for me. But I have the subject for 8 digits and I am not able to manage it. I have numbers from 80000000 to 85000000 in string. They are somtimes in the middle but somtimes also at the end. I was able to extend the solution to 7digits. but if I add on to all mentioned values up to 8 digits, nothing is displayed anymore. Can anybody help?