topic Re: Exception Report - Script question in QlikView

Exception Report - Script question

stuwannop — Tue, 13 May 2014 13:32:38 GMT

HI Everyone

Hope someone can help. In my script I look in a particular field and report "Exception" or "OK" depending on whether a field is blank or not. Formula is simply this:

if(TSGRef = '','Exception','OK') as NoRefException

What I want to do is create a line that will check the "TSGRef" field to see if it contains only 5 numeric characters (essentially a number between 00001 and 99999 put another way.) I would like it to return "Exception" if the data in that field does not contain data matching that criteria.

Anyone help out with this?

Thanks in advance.

Stu

Re: Exception Report - Script question

swuehl — Tue, 13 May 2014 13:40:04 GMT

if(TSGRef >= 1 and TSGRef <= 99999, 'OK','Exception') as NumberException

edit: maybe add a len() check

if(TSGRef >= 1 and TSGRef <= 99999 and len(TSGRef)=5, 'OK','Exception') as NumberException

Re: Exception Report - Script question

stuwannop — Tue, 13 May 2014 13:46:02 GMT

Thanks that's brilliant - I had got the greater than and less sussed just then and it was the LEN check that I didn't know how to do. Many thanks.