Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Boa tarde,
Existe a possibilidade de se utilizar regex em qliksense? na internet só vi para qlikview. Encontrei esse link sobre Regex Connector, mas não informações de como instalar ou utilizar.
O Qlik Web Connectors pode ser baixado aqui.
Há um script que pode ser utilizado para o Regex "[A-Z]*".
// That's the script code:
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
Post Original.
Solved: Regular expression [A-Z] * - Qlik Community - 851408
Don't worry, be Qlik.
Tonial