topic Regular expression [A-Z] * in QlikView

Regular expression [A-Z] *

Tue, 30 Jun 2015 10:00:04 GMT

Hello,

How can I write "[A-Z] * " in Qlikview?

thank you

Re: Regular expression [A-Z] *

swuehl — Tue, 30 Jun 2015 10:06:43 GMT

There is no built in support for regular expressions, but you can use a Macro:

How to use regular expressions

Re: Regular expression [A-Z] *

Tue, 30 Jun 2015 10:18:09 GMT

Actuality, I can't open the file in that site. Could you copy me the code? thank you!

Re: Regular expression [A-Z] *

swuehl — Tue, 30 Jun 2015 10:21:47 GMT

Try this demo:

Regular expressions in the load script - The Qlik Fix! The Qlik Fix!

Re: Regular expression [A-Z] *

Tue, 30 Jun 2015 10:38:07 GMT

Hello, as I'm a beginer, my Qlikview is still a Test Version, so I can't open the files.qvw.

If somme one can give me the code, I'll be very appreciated!

Re: Regular expression [A-Z] *

tiago_hubner — Tue, 30 Jun 2015 14:07:05 GMT

Inputdata:

LOAD * INLINE

[

ID, Customer name, Phone number, Birth date

123ABC, Fred Flintstone, 555-1234, FDJKJR@#09-06-1949#jklgerwnt

456DEF, Wilma Flintstone, 555-5678, .nmrewui93#05-01-1950#kmrem4324

ABC123, Barney Rubble, 555-9101, bhjewri432#07-03-1948#kmtjertv325

789GHI, Betty Rubble, 5551121, 324pfdskm#24-11-1947#nmn43jkj32fd

];

Cleandata:

LOAD

ID,

if(RegExTest([Phone number], '^\d{3}-\d{4}') = -1, 'Yes', 'No') as [Input phone number is valid], // Test if the phone number consists of three digits followed by a hyphen and four digits

RegExReplace([Phone number], '^(\d{3})(\d{4})', '$1-$2') as [Cleaned phone number], // Insert a hyphen between the third and fourth digit, if it is not already there

RegExReplace([Customer name], '^^(\w+)\s(\w+)', '$2, $1') as [Cleaned customer name], // Reformat the customer name in "Lastname, Firstname" format

RegExFind([Birth date] , '\d{2}-\d{2}-\d{4}', ';') as [Cleaned birth date] // Extract the birth date (a string in the form of xx-xx-xxxx) from the unclean, raw data

RESIDENT Inputdata WHERE RegExTest(ID, '^\d{3}[A-Z]{3}'); // Test if the ID consists of 3 digits followed by 3 letters, if not discard the record

Regular expressions in the load script - The Qlik Fix! The Qlik Fix!

Re: Regular expression [A-Z] *

swuehl — Tue, 30 Jun 2015 15:00:08 GMT

// That's the script code:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

Inputdata:

LOAD * INLINE

[

ID, Customer name, Phone number, Birth date

123ABC, Fred Flintstone, 555-1234, FDJKJR@#09-06-1949#jklgerwnt

456DEF, Wilma Flintstone, 555-5678, .nmrewui93#05-01-1950#kmrem4324

ABC123, Barney Rubble, 555-9101, bhjewri432#07-03-1948#kmtjertv325

789GHI, Betty Rubble, 5551121, 324pfdskm#24-11-1947#nmn43jkj32fd

];

Cleandata:

LOAD

ID,

if(RegExTest([Phone number], '^\d{3}-\d{4}') = -1, 'Yes', 'No') as [Input phone number is valid], // Test if the phone number consists of three digits followed by a hyphen and four digits

RegExReplace([Phone number], '^(\d{3})(\d{4})', '$1-$2') as [Cleaned phone number], // Insert a hyphen between the third and fourth digit, if it is not already there

RegExReplace([Customer name], '^^(\w+)\s(\w+)', '$2, $1') as [Cleaned customer name], // Reformat the customer name in "Lastname, Firstname" format

RegExFind([Birth date] , '\d{2}-\d{2}-\d{4}', ';') as [Cleaned birth date] // Extract the birth date (a string in the form of xx-xx-xxxx) from the unclean, raw data

RESIDENT Inputdata WHERE RegExTest(ID, '^\d{3}[A-Z]{3}');

// And you need to copy this VBScript code into the Macro-Editor (CTRL-M should open it in the sheet view).

Function RegExTest(iString, Pattern, IgnoreCase)

' Returns TRUE if Pattern can be matched to iString

' iString: string, the input string to search in

' Pattern: string, the regular expression pattern to search for

' IgnoreCase: boolean, indicates if search should be case-sensitive

set RE = New RegExp

RE.Pattern = Pattern

RE.IgnoreCase = IgnoreCase

RegExTest = RE.Test(iString)

End Function

Function RegExReplace(iString, sPattern, rPattern)

' Replaces any occurence of sPattern within the string iString with rPattern

' and returns the modified string, if no match is found the original string

' is returned

' iString: string, the input string to search and replace in

' sPattern: string, the pattern to search for

' rPattern: string, the pattern to replace the found pattern with

set RE = New RegExp

RE.Pattern = sPattern

RE.Global = True

RegExReplace = RE.Replace(iString, rPattern)

End Function

Function RegExFind(iString, Pattern, Separator, IgnoreCase)

' Returns a string containing the matches that were found by searching for Pattern in iString.

' If more than 1 match was found, the results are separated by the character(s) specified in Separator

' iString: string, the input string to search

' Pattern: string, the pattern to search for

' Separator: string, the character(s) to use for separating results

' IgnoreCase: boolean, indicates if the search should be case-sensitive

set RE = New RegExp

RE.Pattern = Pattern

RE.IgnoreCase = IgnoreCase

RE.Global = True

set Found = RE.Execute(iString)

for i = 0 To Found.Count - 1

Result = Result & Found(i).Value & Separator

next

RegExFind = left(Result, len(Result)-1)

End Function

Re: Regular expression [A-Z] *

evan_kurowski — Mon, 17 Apr 2017 16:22:09 GMT

YAS! These are working very nicely, thank you Stefan. I have worked out too many of these, character by character with the components we do have in the native scripting syntax. This adds an "extra gear", love it!

Re: Regular expression [A-Z] *

Tue, 06 Jun 2017 08:25:25 GMT

Hi Evan,

Could you please help me ? I am new to qlikview.

I am trying to achieve below functionality...

Count of fields from particular column where field consist special character.

Thanks & Regards,

Ashish

Re: Regular expression [A-Z] *

evan_kurowski — Fri, 21 Jul 2017 13:20:03 GMT

Hello Ashish, While I think the drop-in Regex() functions are awesome, if you're only seeking to detect one specific special character, you could do that without Regex.

Something that seems to have been updated between QV 11 & QV 12 is the capability of the chr()/ord() functions to reach into the upper planes of unicode.

QV 11 chr()/ord() function seemed to stop detecting new characters at 65,536.
QV 12 chr()/ord() function may go into planes beyond 2^16 and extend into 2 million characters?

But if you can establish the Ord() of your special character.. for example, let's say seeking chr(50000) 썐

The to get a count of rows in that field containing that specific character could have several syntax options, but one route: =count( {<MyField={'*썐*'}>} MyField)

Re: Regular expression [A-Z] *

evan_kurowski — Fri, 15 Dec 2017 07:39:24 GMT

Stefan Wühl

Function RegExFind(iString, Pattern, Separator, IgnoreCase)

    for i = 0 To Found.Count - 1

        Result = Result & Found(i).Value & Separator

    next



    RegExFind = left(Result, len(Result)-1)



End Function

Hello Stefan,

I have used this thread as reference several times. Not sure if anything changed, but I noticed copying the RegExFind() function verbatim, it was shaving off the last character on searches. does that '-1' belong at the end where it puts the result in the function variable? Maybe something changed on their ordinal system, but I've seen this thread for regex referenced in many other threads, and would hate for the next person to come along and have their regex experience be compromised.

Also, question for you. I've been trying to find out how to reference the character classes. .NET documentation seems to indicate character classes are possible (i.e. \p{IsCyrillic}). However, I'm not finding those same character classes working in the VBscript. Do you know how to invoke the character classes in the VB script regex?

Really appreciate this thread btw, Thank you! ~E

Re: Regular expression [A-Z] *

CYRUS — Wed, 12 Dec 2018 00:41:33 GMT

Thanks to share
But it gives me this error when I run:
Syntax error

Unexpected token: ')', expected one of: ',', ',', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', ...

Cleandata:
LOAD
ID,
if(RegExTest([Phone number], '^\d{3}-\d{4}'>>>>>>)<<<<<< = -1, 'Yes', 'No') as [Input phone number is valid], // Test if the phone number consists of three digits followed by a hyphen and four digits
RegExReplace([Phone number], '^(\d{3})(\d{4})', '$1-$2') as [Cleaned phone number], // Insert a hyphen between the third and fourth digit, if it is not already there
RegExReplace([Customer name], '^^(\w+)\s(\w+)', '$2, $1') as [Cleaned customer name], // Reformat the customer name in "Lastname, Firstname" format
RegExFind([Birth date] , '\d{2}-\d{2}-\d{4}', ';') as [Cleaned birth date] // Extract the birth date (a string in the form of xx-xx-xxxx) from the unclean, raw data
RESIDENT Inputdata WHERE RegExTest(ID, '^\d{3}[A-Z]{3}')

Re: Regular expression [A-Z] *

evan_kurowski — Fri, 18 Jan 2019 23:04:27 GMT

*Note: since the set-analysis quoting change, the wildcard set-analysis match will now be double-quotes =count( {<MyField={"*썐*"}>} MyField)

single quotes would look for the literal 3 character string asterisk-unicode char-asterisk.

Re: Regular expression [A-Z] *

CYRUS — Mon, 06 Jan 2020 00:32:13 GMT

In my case its working, here i attached my script:

WHERE
RegTelecom('$(vCalledNumber)', CalledNumberPattern ,0) AND
RegTelecom('$(vCallingNumber)', CallingNumberPattern ,0) AND
RegTelecom('$(vorigIpv4v6Addr)',origIpv4v6AddrPattern,0) AND
RegTelecom('$(vdestIpv4v6Addr)',destIpv4v6AddrPattern,0)
;

Function RegTelecom(iString, Pattern, IgnoreCase)

set RE = New RegExp
RE.Pattern = Pattern
RE.IgnoreCase = IgnoreCase
RegTelecom = RE.Test(iString)

End Function