topic Using index() to trace multiple Strings? in QlikView

Using index() to trace multiple Strings?

ganeshreddy — Tue, 12 May 2015 11:32:42 GMT

Hi All,

Can anyone tell , how can i find number of occurrences of particular string by using Index(), here i need to take the place where it is occurred as well. in the below example Transfer occurred two times.

Ex: 00:00:00-03:30:00|Transfer|schedTransfe|00:00:00-02:51:30|Break|03:23:00-04:30:00|Transfer|schedTransfe|00:00:00-05:30:00

Cheers,

Ganesh.

Re: Using index() to trace multiple Strings?

datanibbler — Tue, 12 May 2015 11:46:43 GMT

Hi,

you will need to use two different functions to do this:

- The INDEX() function will return the starting position of either the first or any one occurence of the searched_for string
within the total string, but you will have to enter the occurence_nr. (2 for the 2nd one) as a parameter.

- The Subfieldcount() function will return the nr. of occurences of your searched_for string in the total string.

=> You will probably have to use Subfieldcount() first and then build some kind of loop to step through the nr. of occurences and return the starting_position of every one.

HTH

Re: Using index() to trace multiple Strings?

ashwanin — Tue, 12 May 2015 12:07:06 GMT

Hi Ganesh,

You can use Substringcount.

=SubStringCount(F1,'Transfer')

F1 = field where your are searching the field count.

Transfer = String what you are searching

Re: Using index() to trace multiple Strings?

anbu1984 — Tue, 12 May 2015 12:10:42 GMT

Do you need character position or field position (Transfer present in 2nd and 7th field)?

Re: Using index() to trace multiple Strings?

ganeshreddy — Tue, 12 May 2015 13:01:40 GMT

Hi,

Thanks for your replay, here the reason why i am asking for index function is to calculate the meal times. For example take 'Break' from above given string , there is only one break so i can take left instance as out-time and right instance as In time. To calculate Second meal i need to track where is the second 'Break' occurred. Here i a can use subfield function to get the desired result but it is complex because we don't know the exact sequence of the string.

Cheers,

Ganesh.