Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
You probably can, but I would also suggest looking into using regular expressions, maybe start with
Can you share few possibilities from where we are trying to extract this?
Can you post 2 or 4 sample values to build solution?
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:
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'.
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 ];
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
Yes, may be u r rite, but then we can't do any thing till we not get the sample values
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