Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead 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
swuehl
MVP
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:

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

View solution in original post

13 Replies
swuehl
MVP
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!

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!

tiago_hubner
Partner - Contributor III
Partner - Contributor III

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!

swuehl
MVP
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:

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

evan_kurowski
Specialist
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,

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

evan_kurowski
Specialist
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)