10 Replies Latest reply: Dec 15, 2017 2:39 AM by Evan Kurowski RSS

    Regular expression [A-Z] *

      Hello,

       

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

      thank you

        • Re: Regular expression [A-Z] *
          Stefan Wühl

          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] *

            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

                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] *
                  Stefan Wühl

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

                      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] *
                          Ashish Saraf

                          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

                              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

                            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.

                            Regex_macro.png

                             

                            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