String Function Help !

[ganeshreddy]

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.

[marcus_sommer]

You mean substringcount(field, '|') ?

- Marcus

[ganeshreddy]

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.

[jagan]

Hi,

Try like this

=SubStringCount('18:00:00-24:00:00|Transfer|schedTransfer|00:00:00-25:00:00|Break|23:30:00-24:00:00|dayDivide', ':') /2

Dividing by 2 because you 2 : for each punch.

Regards,

jagan.

[marcus_sommer]

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

[ganeshreddy]

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

                                                             or

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

                                                             or

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

[anbu1984]

LOAD *,SubStringCount(Punches,':')/2 + If(Index(Punches,'|dayDivide|'),-4,If(Index(Punches,'dayDivide'),-2,0)) INLINE [
Punches
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
];

[ganeshreddy]

Hi jagan,

can you give me different logic by using above data

cheers,

Ganesh