extract string with INDEX function

[#1]

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?

[#2]

Hi,

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

try this.

- Sridhar

[johnw · Accepted solution]

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)