topic Index Function in QlikView

Index Function

Thu, 30 Oct 2014 14:27:48 GMT

could you please explain how index function working

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

Many Thanks

Re: Index Function

Thu, 30 Oct 2014 14:31:16 GMT

Hi,

left( Date, index( Date,'-') -1 )

Here "index( Date,'-') -1" gives 5-1 which is 4 .

so left( Date,4) gives 1997

mid( Date, index( Date, '-', 2 ) -2, 2 )

Here

index( Date, '-', 2 ) gives the index of second occurence of '-' which is 8

so Mid(Date,8-2,2) = Mid(Date,6,2) = 07

Re: Index Function

JonnyPoole — Thu, 30 Oct 2014 14:33:59 GMT

In your example, the index function is finding the numerical character position of the first ( index( Date,'-') )or second ( index( Date, '-', 2 ) ) instance of the hyphen ( - ) character in the string that is Date.

Then there is a subtraction of that number. In the first case its 5 - 1 = 4. 2nd case its 8-6 = 6

That number is being fed into the left() and Mid() functions to take a piece of the date string.

left( Date , 4) -> first 4 characters of Date = 1997

mid( Date, 6, 2) -> starting at position 6, take next two characters = 07

Re: Index Function

Thu, 30 Oct 2014 14:35:40 GMT

Hi,

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

index(date,'-') it will give by default first position of '-' that is 5 then left(date,5-1) is left(date,4) it will give finally 1997

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

index(date,'-',2) it will give second '-' position that is 8 then 8-2 is 6

mid(date,6,2) it will give 07

Re: Index Function

Thu, 30 Oct 2014 14:35:48 GMT

Thanks Shruthi,

I was not under standing -1 use in firstexp, -2 use second exp .. thanks for help

Re: Index Function

its_anandrjs — Thu, 30 Oct 2014 14:36:32 GMT

Index is a string function

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

In this line index( Date,'-') finds the location of the ('-') symbol means here is 5 then 5-1=4 so from left 4 character picks that is 1997.

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

In this index( Date, '-', 2 ) finds the second occurrence of the ('-') symbol that is * then 8-2 = 6 from 6 picks 2 charater by mid function that is 07

Regards

Anand