Search Sub-String with multiple, similar substrings

Questions & Answers
, , , ,
1

Given a string (from Labels, on the Feature level), there are references for the Program Increment(s)* in the Labels field. In this field, there will be 1 (or none) or, in some cases, 2 or more distinct PI reference values. These indicate the duration, the life of the Feature.

*WHERE PI increment reference in Labels will always start with “pi” followed by a 2 digit number; where the total character length will = 4.

Example of label contents: “projectabc, pi08, projectdef, fiscal2022, pi09, pi10, tenantabc”

What I am trying to achieve here are 2 additional columns in the report; where the 1st indicates the, extracted, Left-most pi##, and the 2nd indicates the Right-most pi##. From the example above, the Start-PI resulting value would be “pi08” and the End-PI value would be “pi10”.

I have experimented with Instr, Len and Extract, but am not getting very far very fast.

Your help is appreciated.

2

Hi,

The solution seems possible with the ExtractString function.

The leftmost occurrence of “pi” in the label list:

ExtractString(
  [Measures].[Issue labels],
  ".*?(^|,)(pi[^,$]+).*",
  2
)

The rightmost occurrence of “pi” in the label list:

ExtractString(
  [Measures].[Issue labels],
  ".*(^|,)(pi[^,$]+).*",
  2
)

The idea behind the regex is to look for the “pi” pattern in the comma list (including the options that it can be at the start of the end). The code for both options is almost identical, except that in the first occurrence, we look using the “reluctant *”, in the last occurrence, we use the “greedy *”.

Kindly,
Janis, eazyBI support

3

Thank you so much. I just saw this, tried the solution and it works well. Thank You!