topic extract string with INDEX function in QlikView

extract string with INDEX function

Thu, 16 Sep 2010 13:05:59 GMT

does anyone know what expression i could use to extract the first part of a document path

-----------------------------------------------------------------------------------------------------------------------

e.g. i want the following document paths returned as

Document Paths

e:\qlikview-corporate\finance\finance - budget reports 2010-11.qvw
e:\qlikview-finance\finance reports.qvw

To be returned as

corporate
finance

------------------------------------------------------------------------------------------------------------------------

The closest expression i have created to do this is

=Lower(mid(DocumentPath,Index(DocumentPath,':',1)+11))

This returns

corporate\finance\finance - budget reports 2010-11.qvw
finance\finance reports.qvw

This is close but i only want the expression to return the bold part (corporate / finance).

Any ideas?

extract string with INDEX function

Thu, 16 Sep 2010 13:17:56 GMT

Hi,

=Subfield(Lower(mid(DocumentPath,Index(DocumentPath,':\',1)+11)),'\',1)

try this.

- Sridhar

extract string with INDEX function

johnw — Thu, 16 Sep 2010 23:55:33 GMT

A few more. I like the textbetween() approach, which I think is the clearest. You can stick lower() in any of them if you want to be certain it's in lower case.

textbetween(DocumentPath,':\qlikview-','\')
subfield(mid(DocumentPath,13),'\',1)
subfield(subfield(DocumentPath,':\qlikview-',2),'\',1)