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]

[gerhardl]

Sorry but I have no idea how to use this? What does "otherfield" refer to?

My initial ("Tom") data table import looks like this:

Tom:

LOAD Date,

    Description1,

    Reference,

    // mid(Reference,Index(Reference,'A_')+1,7) as JC_Acct,    (not used in your proposal)

    Amount

FROM XYZ;

[roger_stone]

It's just a random field I inserted so that you can see that all rows come through, even if I have set the account number to null.

[gerhardl]

I end up with one reference field then returning multiple Strip values in certain cases:

[roger_stone]

I provided the code like that so you could see each stage of the process. You're getting joins between the staging tables and your original data, so you would need to do something like this - note the DROP TABLE statements:

Tom:

LOAD * INLINE [

Indata, OtherField

A130116, B

6610295603088, C

A123542, D

U122545, E

239343, F

ABSA Bank A257286, G

A124282-0837698755, H

ABSA Bank A214405, I

A135358, J

Text A123456 More text, K

Some text U123456 A lot more text, L

665544U654321Text, M

];

Dick:

LOAD

KEEPCHAR(Indata,'AU1234567890') AS Strip1,

OtherField

RESIDENT Tom;

DROP TABLE Tom;

Harry:

LOAD

IF (NOT ISNUM(Strip1), Strip1, NULL()) AS Strip2,

OtherField

RESIDENT Dick;

DROP TABLE Dick;

Steve:

LOAD

IF (LEN(Strip2)=7, Strip2,

  IF ((LEFT(Strip2,1) = 'A' OR LEFT(Strip2,1) = 'U') AND ISNUM(MID(Strip2,2,6)),LEFT(Strip2,7),

  RIGHT(Strip2,7))) AS Strip3,

OtherField

RESIDENT Harry;

DROP TABLE Harry;

[swuehl]

Using regular expression Macro:

'

' Regular Expression function. Returns true if astring matches pattern, false otherwise.

' Uses the VBScript RegExp Object.

'

Function regexTest(astring, pattern)

  Set regEx = New RegExp  ' Create a regular expression object

  regEx.Pattern = pattern  ' Set the pattern

  regexTest = regEx.Execute(astring).Item(0)

End Function

The script boils down to

Accounts:

LOAD *,

  regexTest(Indata,'([AU])([0-9]){6}') as Result

INLINE [

Indata, OtherField

A130116, B

6610295603088, C

A123542, D

U122545, E

239343, F

ABSA Bank A257286, G

A124282-0837698755, H

ABSA Bank A214405, I

A135358, J

Text A123456 More text, K

Some text U123456 A lot more text, L

665544U654321Text, M

A124282-0837698755, N

];