Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Regular expression [A-Z] *

Hello,

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

thank you

1 Solution

Accepted Solutions
MVP
MVP

Re: Regular expression [A-Z] *

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

12 Replies
MVP
MVP

Re: Regular expression [A-Z] *

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

How to use regular expressions

Not applicable

Re: Regular expression [A-Z] *

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

MVP
MVP

Re: Regular expression [A-Z] *

Not applicable

Re: Regular expression [A-Z] *

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
Partner

Re: Regular expression [A-Z] *

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!

MVP
MVP

Re: Regular expression [A-Z] *

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

evan_kurowski
Valued Contributor

Re: Regular expression [A-Z] *

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

Re: Regular expression [A-Z] *

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

Highlighted
evan_kurowski
Valued Contributor

Re: Regular expression [A-Z] *

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)