topic Re: Count with len() in QlikView

Count with len()

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on this.

In have to count the ID is not in the specified format.

The ID should contain the 9 digits. First 2 digits is letters,next 6 digits is numbers,last 1 digit is letter.

BC123456Y

HC456789Z

YU345679D

D15656Y23

12485H6KL

In the above ID D15656Y23 and 12485H6KL are not on the specified format so need to get the count as 2.

sunny_talwar — Thu, 07 Dec 2017 12:24:23 GMT

May be this

Count({<ID = {"=Len(ID) = 9 and IsText(Left(ID, 2) and IsNum(Mid(ID, 3, 6)) and IsText(Right(ID, 1))"}>}ID)

sunny_talwar — Thu, 07 Dec 2017 12:26:44 GMT

There is a typo above, try this

Count({<ID = {"=Len(ID) = 9 and IsText(Left(ID, 2)) and IsNum(Mid(ID, 3, 6)) and IsText(Right(ID, 1))"}>}ID)

zhadrakas — Thu, 07 Dec 2017 12:28:28 GMT

What about

in script:

if(len(trim(ID))=9 AND isText(right(ID,1)) AND isText(left(ID,2)) AND isNum(mid(ID,3,6)), 0,1) as CountWrongID

in Frontend

sum(if(len(trim(ID))=9 AND isText(right(ID,1)) AND isText(left(ID,2)) AND isNum(mid(ID,3,6)), 0,1))

sunny_talwar — Thu, 07 Dec 2017 12:32:02 GMT

I guess the requirement is to count the ID which don't meet the criteria... in the case, just add not() around the whole check

=Count({<ID = {"=not(Len(ID) = 9 and IsText(Left(ID, 2)) and IsNum(Mid(ID, 3, 6)) and IsText(Right(ID, 1)))"}>}ID)

its_anandrjs — Thu, 07 Dec 2017 12:39:24 GMT

Load another filed on model this ways

Replace(if(IsText(Left(ID,2)),1,0) and if(IsText(Right(ID,1)),1,0),-1,1) as Correct

Then count with SET =Count( {<Correct = {1} >} ID)