topic Re: String Function Help ! in QlikView

Re: String Function Help !

ganeshreddy — Wed, 29 Apr 2015 10:44:31 GMT

Hi all ,

its me once again,

how to count instance number(number of punches)

ex: 18:00:00-24:00:00|Transfer|schedTransfer|00:00:00-25:00:00|Break|23:30:00-24:00:00|dayDivide

in the above given data Punches count is 6 , can you help me how to achieve this.

Cheers,

Ganesh.

Re: String Function Help !

marcus_sommer — Wed, 29 Apr 2015 10:58:08 GMT

You mean substringcount(field, '|') ?

- Marcus

Re: String Function Help !

ganeshreddy — Wed, 29 Apr 2015 11:03:21 GMT

Thank for the replay Marcus,

I mean count only instances like 00:00:00

12:30:00

20:00:00 etc on that row

cheers,

ganesh.

Re: String Function Help !

jagan — Wed, 29 Apr 2015 11:15:59 GMT

Hi,

Try like this

Dividing by 2 because you 2 : for each punch.

Regards,

jagan.

Re: String Function Help !

marcus_sommer — Wed, 29 Apr 2015 11:16:40 GMT

It will be depend on the logic and homogenous from these records. Maybe it's enough to change the delimiter to '-':

substringcount(field, '-')

or something like:

rangesum(isnum(subfield(field, '|', 1)), isnum(subfield(field, '|', 2)), ...) * - 1

- Marcus

Re: String Function Help !

ganeshreddy — Wed, 29 Apr 2015 11:30:50 GMT

Hi Jagan your exp working fine but what to do i got a constrain like, . If there exists the text “dayDivide” between any two pipes, exclude the out punch to the left of the pipe and the in punch to the right of the pipe when counting the number of punches.

ex: 00:00:00-02:51:30|Break|03:23:00-04:00:00|dayDivide|04:30:30-12:07:00

00:00:00-02:51:30|dayDivide|03:23:00-04:00:00|Break|04:30:30-12:07:00|transfer

00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|dayDivide

Re: String Function Help !

anbu1984 — Wed, 29 Apr 2015 11:50:19 GMT

Re: String Function Help !

ganeshreddy — Wed, 29 Apr 2015 13:10:48 GMT

Hi jagan,

can you give me different logic by using above data

cheers,

Ganesh