Return substring from Field

[gerhardl]

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]

[Chanty4u]

hi,

hope this heps u

https://community.qlik.com/message/827863#827863

[arulsettu]

try this

keepchar(field name,'0123456789') as newfiled

[gerhardl]

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.

[gerhardl]

This will keep all numbers - please see my example above.

[jonathandienst]

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;

[gerhardl]

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?

[swuehl]

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

[roger_stone]

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.

[sunny_talwar]

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