Solved: Extract 6-digit number string from text fied - Qlik Community

Report Inappropriate Content · ‎2016-03-30

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

swuehl · ‎2016-03-30

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

];

View solution in original post

swuehl · ‎2016-03-30

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

How to use regular expressions

sunny_talwar · ‎2016-03-30

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

Kushal_Chawda · ‎2016-03-30

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

swuehl · ‎2016-03-30

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

Report Inappropriate Content · ‎2016-03-30

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'.

Kushal_Chawda · ‎2016-03-30

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

sunny_talwar · ‎2016-03-30

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

Kushal_Chawda · ‎2016-03-30

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

Report Inappropriate Content · ‎2016-03-30

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