topic SQL function "Position" in QlikView in QlikView

SQL function "Position" in QlikView

Wed, 16 Jun 2010 07:59:00 GMT

I'd like to count the times five colums has the value Y.

In SQL it's like:

select icknr,????????????????????????????????????????

position('Y' in iclvd1) + position('Y' in iclvd2) +??

position('Y' in iclvd3) + position('Y' in iclvd4) +??

position('Y' in iclvd5) as n_deliverydays??????????????????

from bisprod/bisicl01????????????????????????????????

where icftg = 20?????????????????????????????????????

SQL function "Position" in QlikView

sparur — Wed, 16 Jun 2010 09:15:43 GMT

Hello.

index( s1 , s2 [ , n ] )

Position of a substring. This function gives the starting position of the n:th occurrence of substring s2 in string s1. If n is omitted, the first occurrence is assumed. If n is negative, the search is made starting from the end of string s1. The result is an integer. The positions in the string are numbered from 1 and up.

Examples:

index( 'abcdefg', 'cd' ) returns 3

index( 'abcdabcd', 'b', 2 ) returns 6

SQL function "Position" in QlikView

tanelry — Wed, 16 Jun 2010 09:25:38 GMT

"index" function shoud be QV's equivalent for "position".
And to count you can use rangesum or rangecount function.

=rangesum(
if(index(iclvd1,'Y')>0,1),
if(index(iclvd2,'Y')>0,1),
...)

or maybe simply:

=rangesum(
if(iclvd1='Y',1),
if(iclvd2='Y',1),
...)