9 Replies Latest reply: Jul 21, 2017 9:20 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)