Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Not applicable

## Regular expression [A-Z] *

Hello,

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

thank you

1 Solution

Accepted Solutions
MVP

// 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:

[

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:

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

13 Replies
MVP

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

How to use regular expressions

Not applicable
Author

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

MVP
Not applicable
Author

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!

Partner - Contributor III

Inputdata:

[

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:

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!

MVP

// 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:

[

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:

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

Specialist

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!

Not applicable
Author

Hi Evan,

I am trying to achieve below functionality...

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

Thanks & Regards,

Ashish

Specialist

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)

Community Browser