Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm trying to find a way to extract account numbers from a field that can contain additional information. An account number will always start with an "A" or a "U" and then have 6 numbers, e.g. A123456 or U654321.
A field can have just the account number, or it can have additional data, e.g. "ADT CASH DEPOSIT A235999" or "FNB APP PAYMENT FROM A230275" - the account number can be on it's own, orat the beginning, middle or end of the string.
I have a function in Excel to do this but not sure how to approach it in Qlikview - want to do this in the load script and then create a new field with the extracted value, e.g:
LOAD
[Original Field],
"substring from field" as [New Filed]
try this
keepchar(field name,'0123456789') as newfiled
Hi,
It's a start but not 100% what I need. Currently I have this:
LOAD Date,
Description1,
Reference,
mid(Reference,Index(Reference,'A_')+1,7) as JC_Acct,
Amount
it works in some cases, but it returns ANY value where it finds an "A" but I need to specifically look for an A or a U followed by 6 NUMBERS. For instance, if the field contains A124282-0837698755 it will return the correct value, but if the field contains ABSA Bank A257286 it will return "ABSA Ba" when I want it to return "A257286" - see image attached. If It can't find anything it should return null.
This will keep all numbers - please see my example above.
The most generic way to do this is something like this:
//---- Split the field into tokens (assumes that data is in table called Data)
T_Accounts1:
LOAD Original,
SubField(Original, ' ') As Tokens
Resident Data;
//---- filter the tokens for account numbers
T_Accounts2:
LOAD Original,
Token as Account
Resident T_Accounts1
Where WildMatch(Token, 'A*', 'U*')
And Len(Token) = 7
And IsNum(Mid(Token, 2, 7));
//---- Add back to main data table. The Group By will prevent multiples from adding rows to Data
// if there is more than one account number in the tokens
Left Join (Data)
LOAD Original,
MinString(Account) As Account
Resident T_Accounts2
Group by Original;
//---- Clean up
DROP Table T_Accounts1, T_Accounts2;
Hi Jonathan,
I'll try this now but my first concern is that the substring I need is not necessarily split from the rest of the field by a space. A field may for instance be "A124282-0837698755"
This is basically free text for a customer making a bank payment and they do weird stuff which they think helps...
Can I use this but split into tokens using more than one delimiter?
Maybe a regular expression would be best here. You can add support for regular expressions using a small macro that you can call from your script, see for example
http://qlikviewcookbook.com/download/regular-expression-pattern-matching/
How to use regular expressions
Regular-Expressions.info - Regex Tutorial, Examples and Reference - Regexp Patterns
Something like this?
Not my most elegant code and you probably do it in fewer steps, but its possible to do what you want without too much pain.
Did not even know that this kind of thing existed (even out of QlikView). Thanks for sharing this Stefan. I am sure I will need to use this sometime in the future.
Sunny