cancel
Showing results for 
Search instead for 
Did you mean: 

partial match

Highlighted
06 Trekker

partial match

hi Team, 

 

im using Base24station record. i want to display total count station not connected based on #issuer prompt 

 

ex,  #issuer NCLB 

stations configured below patteren 

S1A^NC1C^PS01

S1A - indicates node 

^      - Symbol 

NC  - 1st 2 char of #issuer 

1      - Site indicator 

C      - indicate Client 

^      -  Symbol 

PS     - POS service 

01    - station sequence number 

 

here based on first 2 charactor of #issuer and "PS" filter out and count the number station connected 

 

im not sure how to use MATCHS  REGEX for this case 

4 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Re: partial match

Hi NETS,

I'm going to assume you're drilling down from a transaction type display / dashboard that shows issuer FIID (e.g. NCLB) and you want to show the subset of XPNET stations that match your criteria (e.g. chars 5 - 6 of station name match chars 1 - 2 of the issuer FIID and chars 10 - 11 are 'PS')

 

I'm also going to assume some knowledge of working with Prognosis displays, including how prompts and drilldowns work.

 

You can't use the raw issuer FIID string to give the where clause you want in your Base24Station (child) display, but it's reasonably easy to create the string you need in the parent display (i.e. the display you drill down from) instead.

You can then pass that string in a separate 'prompt' variable and use that directly in your where clause.

 

I'm going to show you how to do that using MATCHES REGEX, because that's what you asked about and because you have the most control and most reliable results that way.  However, it's a bit of overkill and you could just as well use MATCHES instead.  (with the benefit that you don't need to understand regexs then!)

 

The regular expression that meets your criteria is:

^\w{3}\^NC\w{2}\^PS\d{2}$

(the 'NC' is an example and will be whatever the two character issuer prefix is)

IR doesn't teach regexs because they are a standard syntax with lots of help available elsewhere.  The variant we use is 'PCRE' and if you search for help on this you will find many hits.  A favourite site of mine is regex101.com, because it explains the syntax and also allows you to test your regex.

Very briefly, the regex above can be broken down as:

- first 3 chars are alphanumeric 

^\w{3}

- next 3 chars are "^NC"

\^NC

- next 2 chars are alphanumeric

\w{2}

- next 3 chars are "^PS"

\^PS

- last 2 chars are numeric

\d{2}$

To use this regex to filter based on the issuer 'drilled down' on in the parent display:

 

Step 1 - Build the regex in the parent display

We need to build it in the parent display because it will be different depending on which issuer needs to be selected.

To to this we create a user defined field in the parent display with the following calculated field syntax:

="^\w{3}\^" + substring(record.field , 0, 2) + "\w{2}\^PS\d{2}$"

where record.field is the name of the record and field containing the issuer FIID, e.g. Base24PosTransactionSummary.IssuerFiid

e.g.   ="^\w{3}\^" + substring(Base24PosTransactionSummary.IssuerFiid, 0, 2) + "\w{2}\^PS\d{2}$"

 

As you can see, I've just taken the regex above and used the concatenate '+' operator to join in the correct two characters for the issuer.  These are taken from the relevant field using the substring operator, to extract the first two characters.

 

As I said, I'm assuming some knowledge of displays so I won't go into specifics of creating a user defined field, as it's already a long reply, but the result should look something like this:  (I'm using a different record / field for ease of testing)

 

Screenshot_20201027_153949.png

Note down the name of the user defined field you create (e.g. .userdef01) because you will need it in the next step

 

You can test that your regex is being built correctly by running the modified parent display.  The regex will be in the new .userdef01 (or whatever your new user defined field is called) column:

Screenshot_20201027_160115.png

 

Once you are satisfied, you can clean up your parent display by hiding that column.  (it will still be available for the drill down)

Screenshot_20201027_160232.png

 

Step 2 - Pass the regex in your drilldown

Next you need to edit the drilldown (the one you're using to link from the Issuer display to the XPNET display) to pass an extra parameter.  While it would be possible to do this in the #issuer prompt your display is already using, it would be confusing and it also may break other parts of the display.  Therefore you should pass the regex in a separate prompt variable.  I'm using one called #issrregex.

 

Therefore, add a new substitution to your drilldown with 'substitute' set to .userdef01 (or whatever your user defined field name was above) and 'Into Prompt' set to #issrregex.  (you may need to add it to the prompt table first in order to select it)

Screenshot_20201027_154345.png

Almost there!

 

Step 3 - Use the prompt in your child display

All you need to do now is use the value of the prompt directly in the MATCHES REGEX in your child display.  e.g., for your XPSTA example:

STN MATCHES REGEX #issrregex

I've used a different record so my example looks a little different:

Screenshot_20201027_155546.png

 

Again, I've left out a lot of detail on how to do these steps.  This is covered in the Prognosis Administrator Training course but do let us know if more help is needed, I'm sure there's plenty of people who can assist Smiley Happy

View solution in original post

Highlighted

Re: partial match

And the 'simple' version using MATCHES (instead of MATCHES REGEX) would be the same except:

 

The user defined field in the parent display should contain a simple wildcard pattern:

="???^" + substring(Record.Field , 0, 2) + "??^PS??"

e.g.:

="???^" + substring(Base24PosTransactionSummary.IssuerFiid , 0, 2) + "??^PS??"

 

The where clause in the child display should use MATCHES (instead of MATCHES REGEX), e.g.:

STN MATCHES #issrregex

View solution in original post

Highlighted
06 Trekker

Re: partial match

hi Peter 

 

Thanks for such a detail explanation , really very help to understand concept. actually im trying to get data from userdef01 value (contains FIID as text)  first 2 character in userdef02.  

Is it possible to use it in substring ?   

 

actually i didnt use  Base24PosTransactionSummary record, just want play with pre define FIID from userdef01

 

 

 

 

image.png

 

 

 

 

image.png

View solution in original post

Highlighted

Re: partial match

Hi NETS,

This is a strange 'quirk' in user defined fields.  To 'fix' it you need to make the source used defined field a calculated field.

i.e. instead of defining USERDEF01 as a 'plain' user defined field:

DBSC

Instead create it as a 'calculated' field:

='DBSC'

The result is the same when you are just displaying the field, but calculated fields don't like including 'plain' user defined fields.

 

If you change the definition of USERDEF01 like this, USERDEF02 should work as expected

View solution in original post

11 REPLIES 11
Highlighted

Re: partial match

Hi NETS,

I'm going to assume you're drilling down from a transaction type display / dashboard that shows issuer FIID (e.g. NCLB) and you want to show the subset of XPNET stations that match your criteria (e.g. chars 5 - 6 of station name match chars 1 - 2 of the issuer FIID and chars 10 - 11 are 'PS')

 

I'm also going to assume some knowledge of working with Prognosis displays, including how prompts and drilldowns work.

 

You can't use the raw issuer FIID string to give the where clause you want in your Base24Station (child) display, but it's reasonably easy to create the string you need in the parent display (i.e. the display you drill down from) instead.

You can then pass that string in a separate 'prompt' variable and use that directly in your where clause.

 

I'm going to show you how to do that using MATCHES REGEX, because that's what you asked about and because you have the most control and most reliable results that way.  However, it's a bit of overkill and you could just as well use MATCHES instead.  (with the benefit that you don't need to understand regexs then!)

 

The regular expression that meets your criteria is:

^\w{3}\^NC\w{2}\^PS\d{2}$

(the 'NC' is an example and will be whatever the two character issuer prefix is)

IR doesn't teach regexs because they are a standard syntax with lots of help available elsewhere.  The variant we use is 'PCRE' and if you search for help on this you will find many hits.  A favourite site of mine is regex101.com, because it explains the syntax and also allows you to test your regex.

Very briefly, the regex above can be broken down as:

- first 3 chars are alphanumeric 

^\w{3}

- next 3 chars are "^NC"

\^NC

- next 2 chars are alphanumeric

\w{2}

- next 3 chars are "^PS"

\^PS

- last 2 chars are numeric

\d{2}$

To use this regex to filter based on the issuer 'drilled down' on in the parent display:

 

Step 1 - Build the regex in the parent display

We need to build it in the parent display because it will be different depending on which issuer needs to be selected.

To to this we create a user defined field in the parent display with the following calculated field syntax:

="^\w{3}\^" + substring(record.field , 0, 2) + "\w{2}\^PS\d{2}$"

where record.field is the name of the record and field containing the issuer FIID, e.g. Base24PosTransactionSummary.IssuerFiid

e.g.   ="^\w{3}\^" + substring(Base24PosTransactionSummary.IssuerFiid, 0, 2) + "\w{2}\^PS\d{2}$"

 

As you can see, I've just taken the regex above and used the concatenate '+' operator to join in the correct two characters for the issuer.  These are taken from the relevant field using the substring operator, to extract the first two characters.

 

As I said, I'm assuming some knowledge of displays so I won't go into specifics of creating a user defined field, as it's already a long reply, but the result should look something like this:  (I'm using a different record / field for ease of testing)

 

Screenshot_20201027_153949.png

Note down the name of the user defined field you create (e.g. .userdef01) because you will need it in the next step

 

You can test that your regex is being built correctly by running the modified parent display.  The regex will be in the new .userdef01 (or whatever your new user defined field is called) column:

Screenshot_20201027_160115.png

 

Once you are satisfied, you can clean up your parent display by hiding that column.  (it will still be available for the drill down)

Screenshot_20201027_160232.png

 

Step 2 - Pass the regex in your drilldown

Next you need to edit the drilldown (the one you're using to link from the Issuer display to the XPNET display) to pass an extra parameter.  While it would be possible to do this in the #issuer prompt your display is already using, it would be confusing and it also may break other parts of the display.  Therefore you should pass the regex in a separate prompt variable.  I'm using one called #issrregex.

 

Therefore, add a new substitution to your drilldown with 'substitute' set to .userdef01 (or whatever your user defined field name was above) and 'Into Prompt' set to #issrregex.  (you may need to add it to the prompt table first in order to select it)

Screenshot_20201027_154345.png

Almost there!

 

Step 3 - Use the prompt in your child display

All you need to do now is use the value of the prompt directly in the MATCHES REGEX in your child display.  e.g., for your XPSTA example:

STN MATCHES REGEX #issrregex

I've used a different record so my example looks a little different:

Screenshot_20201027_155546.png

 

Again, I've left out a lot of detail on how to do these steps.  This is covered in the Prognosis Administrator Training course but do let us know if more help is needed, I'm sure there's plenty of people who can assist Smiley Happy

View solution in original post

Highlighted

Re: partial match

And the 'simple' version using MATCHES (instead of MATCHES REGEX) would be the same except:

 

The user defined field in the parent display should contain a simple wildcard pattern:

="???^" + substring(Record.Field , 0, 2) + "??^PS??"

e.g.:

="???^" + substring(Base24PosTransactionSummary.IssuerFiid , 0, 2) + "??^PS??"

 

The where clause in the child display should use MATCHES (instead of MATCHES REGEX), e.g.:

STN MATCHES #issrregex

View solution in original post

Highlighted
06 Trekker

Re: partial match

hi Peter 

 

Thanks for such a detail explanation , really very help to understand concept. actually im trying to get data from userdef01 value (contains FIID as text)  first 2 character in userdef02.  

Is it possible to use it in substring ?   

 

actually i didnt use  Base24PosTransactionSummary record, just want play with pre define FIID from userdef01

 

 

 

 

image.png

 

 

 

 

image.png

View solution in original post

Highlighted
06 Trekker

Re: partial match

Hi Peter 

 

i manage to get userdef01 field using meta.userdef01 in uderdef02. 

meta.userdef01 = DBSC

but substring (meta.userdef01, 0, 2) unable to filter to "DB" and displayed in Dashboard , it displayed fully and i attached here for reference. 

 

image.pngimage.png

Highlighted
06 Trekker

Re: partial match

Hi Peter 

 

i managed to get prompt value DB in #IssuerStn

 

if i use below condition in child DP , no station listed out 

STN MATCHES REGEX "^\w{3}\^#IssuerStn\w{4}\^PS\d{2}\w{1}$" AND CURRSTAT NOT CONTAINS "STARTED"

 

but if directly mention DB in matchregex , i'm able to see station details. 

STN MATCHES REGEX "^\w{3}\^DB\w{4}\^PS\d{2}\w{1}$" AND CURRSTAT NOT CONTAINS "STARTED"

 

is it general text and string both are different ? 

 

Highlighted
06 Trekker

Re: partial match

Finally achieved 

Thanks 

 

Highlighted

Re: partial match

Hi NETS,

This is a good question.  A string is just general text, but it can't include a prompt.  You can use a prompt anywhere you would use a string or numeric constant in a where clause (and quite a few other places in a display), but not within another string.

 

That's why we need to build the entire regex in the parent display, so we can use a where clause like:

STN MATCHES REGEX #issrregex AND CURRSTAT NOT CONTAINS "STARTED"

The #issrregex prompt will already contain (in your case) '^\w{3}\^DB\w{4}\^PS\d{2}\w{1}$'

 

Highlighted

Re: partial match

Hi NETS,

This is a strange 'quirk' in user defined fields.  To 'fix' it you need to make the source used defined field a calculated field.

i.e. instead of defining USERDEF01 as a 'plain' user defined field:

DBSC

Instead create it as a 'calculated' field:

='DBSC'

The result is the same when you are just displaying the field, but calculated fields don't like including 'plain' user defined fields.

 

If you change the definition of USERDEF01 like this, USERDEF02 should work as expected

View solution in original post

Highlighted

Re: partial match

Great to hear!  Thanks for the update

 

I replied to your questions anyway to help others who might be following