Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Scripting using RegEx

Hi,

I wanted to copy the following SQLServer statement in my script:

WHEN @Select(Injury Detail\Injury Codes) LIKE '[289]D[0123456789][1023456789]'

From searching the web it appears that RegEx is the best solution. I went to the following web site

http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

and using the example I copied the code from the module editor as directed.

I then created the following in my script:

Injurydata:

LOAD * INLINE

     [

     IC

     8132

     9D04

     7a01

     2D01

     7A02

     8D14

     ];

     

//valid?

ICode:

LOAD

     IC,

    if(RegExTest(IC, '^[289]D[0-9]$')= -1, 'Yes', 'No') as ValidOD       Resident Injurydata; This code works as required. I then tried to use the same code on the database. The SQL load below works without the RegExTest code, but when I add the RegExTest code the load freezes : // STAT table - 700RXX Injury: LOAD CLAIM_NO & CLAIMANT_NO as ClaimNo,      INJURYCODES; SQL SELECT     CLAIM_NO,     CLAIMANT_NO,     INJURYCODES FROM DB700RXX.dbo.STAT; //Create detail InjuryCode: LEFT JOIN (Injury) LOAD ClaimNo,      if(RegExTest(INJURYCODES, '^[289]D[0-9]$')= -1, 'Yes', 'No') as ValidOD

Resident Injury ;

I am using exactly the same code as in the in line load on the same Qlikview document, so can someone tell me why the RegExTest code does not work with the SQLServer load?

Thanks

Jason

1 Reply
Not applicable

Re: Scripting using RegEx

I have found the solution, so just in case someone has the same question in the future:

The RegExTest has 3 parameters and the third one appears to be optional, but if it is not specified the performance is very poor. The 3 parameter is IgnoreCase, 1 for yes and 0 for no. So the following processed much quicker:

if(RegExTest(INJURYCODES, '^[289]D[0-9]{2}$',1)= -1, 'Yes','No') as ValidOD

Jason