Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Re: String Function Help !

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.

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: String Function Help !

You mean substringcount(field, '|') ?

- Marcus

Highlighted
Creator III
Creator III

Re: String Function Help !

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: String Function Help !

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: String Function Help !

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

Highlighted
Creator III
Creator III

Re: String Function Help !

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

Highlighted
Master III
Master III

Re: String Function Help !

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
]
;

Highlighted
Creator III
Creator III

Re: String Function Help !

Hi jagan,

can you give me different logic by using above data

cheers,

Ganesh