topic Re: Return substring from Field in QlikView

Return substring from Field

gerhardl — Fri, 11 Dec 2015 08:01:32 GMT

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]

Re: Return substring from Field

Chanty4u — Fri, 11 Dec 2015 08:07:43 GMT

hi,

hope this heps u

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

Re: Return substring from Field

arulsettu — Fri, 11 Dec 2015 08:11:19 GMT

try this

keepchar(field name,'0123456789') as newfiled

Re: Return substring from Field

gerhardl — Fri, 11 Dec 2015 08:15:55 GMT

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.

Re: Return substring from Field

gerhardl — Fri, 11 Dec 2015 08:16:25 GMT

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

Re: Return substring from Field

jonathandienst — Fri, 11 Dec 2015 08:25:52 GMT

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;

Re: Return substring from Field

gerhardl — Fri, 11 Dec 2015 08:30:24 GMT

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?

Re: Return substring from Field

swuehl — Fri, 11 Dec 2015 08:52:52 GMT

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

Re: Return substring from Field

roger_stone — Fri, 11 Dec 2015 11:38:54 GMT

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.

Re: Return substring from Field

sunny_talwar — Fri, 11 Dec 2015 12:08:27 GMT

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

Re: Return substring from Field

gerhardl — Fri, 11 Dec 2015 12:16:35 GMT

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;

Re: Return substring from Field

roger_stone — Fri, 11 Dec 2015 12:27:05 GMT

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.

Re: Return substring from Field

gerhardl — Fri, 11 Dec 2015 12:37:25 GMT

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

Re: Return substring from Field

roger_stone — Fri, 11 Dec 2015 13:10:43 GMT

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;

Re: Return substring from Field

swuehl — Mon, 14 Dec 2015 15:06:09 GMT

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

];