Complex trapping Monarch 14
Altair EmployeeHello everyone,
I am quite new with Monarch, but I already found the Rex feature and I am starting to enjoy this wonderful application, in full version already : )
I am confronting with this challenge, where I sometimes need a floating, an expandable field based on the necessity, something more alive than the basic trap and fixed field.
Here is a basic example:
Employee1:
Cost location
From 01.2011
Cost location number 300
Cost location name Sales
Employee2.
Cost location
From 01.2011
Cost location number 100
Cost location name HR
From 01.2012
Cost location number 400
Cost location name Finance
So trapping `Cost location` and setting a field for the data of Employee1 will not be enough as we need to capture more for Employee2, and even more for others.
I started to learn Regex, could that be a solution here? Is it a way to do this intelligent trapping in Monarch.
Thank you very much for your help or at least for the spent time reading this.
Best regards,
Radu
Answers
-
Radu,
You are on the right track with Regex traps. In order to have your Regex trap be dynamic, you will want to use "Explicit Capture" in the options drop-down box. You will then need to modify your Regex language to incorporate "Explicit Capture." For instance, see below:
Capture: From 01.2012
WITHOUT EXPLICIT CAPTURE
From\s\d{2}\.\d{4}
WITH EXPLICIT CAPTURE CHECKED (change "ColumnName" to whatever you wish your field to be called)
From\s(?<ColumnName>\d{2}\.\d{4})
When you use Explicit Capture, you name the value you wish to trap. You will see it highlight in yellow and you can right click on that and make a field from it. Just be sure to expand the template width enough to capture your longest data element. The trap will only pull that exact pattern and nothing beyond it...very powerful.
Reach out with any questions.
Austin
0 -
Radu,
If your Cost Location records are consistently positions across a row and always have the same number of rows for each change than you can think of the cost location data with the date(s) as you DETAIL record and the Employee information as Append information.
Based on what you have described I would think that should be the easiest approach.
If, however, the data you need to use to trap moves across the row then possibly the Regex approach is required.
It is always good to make the templates as simple as possible.
HTH.
Grant
Edited to correct some really annoying typos!
0 -
Altair Forum User said:
Radu,
You are on the right track with Regex traps. In order to have your Regex trap be dynamic, you will want to use "Explicit Capture" in the options drop-down box. You will then need to modify your Regex language to incorporate "Explicit Capture." For instance, see below:
Capture: From 01.2012
WITHOUT EXPLICIT CAPTURE
From\s\d{2}\.\d{4}
WITH EXPLICIT CAPTURE CHECKED (change "ColumnName" to whatever you wish your field to be called)
From\s(?<ColumnName>\d{2}\.\d{4})
When you use Explicit Capture, you name the value you wish to trap. You will see it highlight in yellow and you can right click on that and make a field from it. Just be sure to expand the template width enough to capture your longest data element. The trap will only pull that exact pattern and nothing beyond it...very powerful.
Reach out with any questions.
Austin
Hi Austin,
Thank you very much for the answer, along with Grant´s answer I pretty much nailed it : )
Just another quick question and i promise to dig in Jive before asking another one : ), do you know if it is possible to use a RegEx on two rows? I mean is there any meta-character which can make the Expression to jump on a second and then on a third row/line? Something which acts like hitting return.
Have a nice week ahead,
Radu
0 -
Altair Forum User said:
Hi Austin,
Thank you very much for the answer, along with Grant´s answer I pretty much nailed it : )
Just another quick question and i promise to dig in Jive before asking another one : ), do you know if it is possible to use a RegEx on two rows? I mean is there any meta-character which can make the Expression to jump on a second and then on a third row/line? Something which acts like hitting return.
Have a nice week ahead,
Radu
Radu,
I haven't had to deal with that situation before so I can't be too confident in an answer. There are some better RegEx experts out here that will hopefully chime in and be able to lend a hand. Happy to hear you got everything working. The other "Perkins" wins again...
Austin
0 -
Altair Forum User said:
Radu,
I haven't had to deal with that situation before so I can't be too confident in an answer. There are some better RegEx experts out here that will hopefully chime in and be able to lend a hand. Happy to hear you got everything working. The other "Perkins" wins again...
Austin
Hi Austin,
Not being a Regex expert I still feel a great need to seek the simplest solutions!
The downside of that approach is that, as yet, I have not been forced to become a Regex expert!
This may not be a good thing.
I did find an good used for a DPS Regex function a couple of days ago as part of a lookup. The only downside was that to get maximum benefit would have meant having the lookup unique key working first whereas I needed the Function to make the key work. So just a secondary deployment for filtering after the initial lookup link for now.
The best part was that I could simply call the function - no need to work out the technical syntax.
That makes some of the Regex power available to everyone without needing technical expertise. So you should have little competition for the most difficult challenges - easy wins! (And greater added value ....)
Grant
0 -
Hi Radu,
I'm glad you got it to work.
Simple, where possible, means easier maintenance and enhancement in the future so it's always good to try to keep things simple.
Then again, the challenges can be fun too .....
Grant
0 -
Altair Forum User said:
Hi Austin,
Not being a Regex expert I still feel a great need to seek the simplest solutions!
The downside of that approach is that, as yet, I have not been forced to become a Regex expert!
This may not be a good thing.
I did find an good used for a DPS Regex function a couple of days ago as part of a lookup. The only downside was that to get maximum benefit would have meant having the lookup unique key working first whereas I needed the Function to make the key work. So just a secondary deployment for filtering after the initial lookup link for now.
The best part was that I could simply call the function - no need to work out the technical syntax.
That makes some of the Regex power available to everyone without needing technical expertise. So you should have little competition for the most difficult challenges - easy wins! (And greater added value ....)
Grant
Hi Grant,
That is fantastic, so there is more when you ask for it : ) and it´s not the soda. Can you please be kind and tell us a bit more about the DPS Regex (what does it mean?)
Furthermore, you were saying that you are calling the function, do you do this in the Table Design? Is that the solution to use a Regex on two rows?(I mean to use Regex to capture a field and then a Formula to obtain other Data under the filed created with Regex)
I hope I am not confused and confusing.
A short example would be really appreciated for our humble understanding : )
Have a nice weekend,
Radu
0 -
Altair Forum User said:
Hi Grant,
That is fantastic, so there is more when you ask for it : ) and it´s not the soda. Can you please be kind and tell us a bit more about the DPS Regex (what does it mean?)
Furthermore, you were saying that you are calling the function, do you do this in the Table Design? Is that the solution to use a Regex on two rows?(I mean to use Regex to capture a field and then a Formula to obtain other Data under the filed created with Regex)
I hope I am not confused and confusing.
A short example would be really appreciated for our humble understanding : )
Have a nice weekend,
Radu
Hi Radu,
Both Data Prep Studio (DPS) and Monarch Classic (Table design) have a Function available for Calculated fields (Formula Fields) called RegexIsMatch.
This allows you do enter a string as a parameter and get a YES/NO result (1/0) about whether that string exists in another field that you specify to be searched.
The string to search FOR can be fixed characters, a Regex formula, the contents of another field or even a version of the contents of another field modified by some other functions and formulas.
The Regex code string functionality is built in to the function for situations where people do not want (or do not need) to createit themselves.
Used with care this can be a very useful tool.
For example I have 2 reports that need to be compared to identify records that have no matches between the reports. (Or do so by identifying records that do match and then filtering those out.)
Part of the Key I need to match the records between the filed is presented differently in the reports ans there is no simple rule that covers every difference that might be possible. However it is possible to identify a string of characters in one file that will appear in the other file where records match.
Lets sy, to make it simple, I have a 6 (or less) character field in one file (A) where the contents should exist in a 12 character field in the second file (B) but not always in the same position in the 12 character field.
RegexIsMatch will find all matches between the fields where the character string in A occurs somewhere in the field B. No other information is required.
This is a very simple example but extremely powerful for a regular user who claims no "Power Data Processing " skills.
By extending the concept of calculated field usage much more powerful match identification concepts can be deployed - often without any need for Regex knowledge at all.
Let me know if this makes sense and is understandable. If not I will try to re-present it in more accessible form if you can tell me which parts are difficult to understand.
Grant
0 -
Altair Forum User said:
Hi Radu,
Both Data Prep Studio (DPS) and Monarch Classic (Table design) have a Function available for Calculated fields (Formula Fields) called RegexIsMatch.
This allows you do enter a string as a parameter and get a YES/NO result (1/0) about whether that string exists in another field that you specify to be searched.
The string to search FOR can be fixed characters, a Regex formula, the contents of another field or even a version of the contents of another field modified by some other functions and formulas.
The Regex code string functionality is built in to the function for situations where people do not want (or do not need) to createit themselves.
Used with care this can be a very useful tool.
For example I have 2 reports that need to be compared to identify records that have no matches between the reports. (Or do so by identifying records that do match and then filtering those out.)
Part of the Key I need to match the records between the filed is presented differently in the reports ans there is no simple rule that covers every difference that might be possible. However it is possible to identify a string of characters in one file that will appear in the other file where records match.
Lets sy, to make it simple, I have a 6 (or less) character field in one file (A) where the contents should exist in a 12 character field in the second file (B) but not always in the same position in the 12 character field.
RegexIsMatch will find all matches between the fields where the character string in A occurs somewhere in the field B. No other information is required.
This is a very simple example but extremely powerful for a regular user who claims no "Power Data Processing " skills.
By extending the concept of calculated field usage much more powerful match identification concepts can be deployed - often without any need for Regex knowledge at all.
Let me know if this makes sense and is understandable. If not I will try to re-present it in more accessible form if you can tell me which parts are difficult to understand.
Grant
Hi Grant,
1.
thanks again for the complex answer, and Yes I now find Calculated Fields very helpful. I actually did some testing today and the RegexExtract is fantastic.
However, taking a step back and looking at my example from the beginning, I am not sure if it is possible to instruct the function to search for the last matched given pattern in a string.
Or is it?
RegexExtract(string,pattern[,N]) Scans the given string for matches to the given regular expression pattern. Returns the text of the Nth match found. If N is not given, this function returns the text of the first match found, if any.
I mean to set the function to reflect in field the last matched pattern. (However, I did create some 2 other columns with the 2nd and 3rd matched pattern, and then I created a reverse nested IF and I obtained the last matched pattern for each employee)
2.
Power Data Processing, do you recommend some good trainings, online resources for this? Stuff that could help me use Monarch better?
Thank you again for the attention and answers, it was a good week for Monarch thanks to you guys.
Have a nice weekend,
Radu
0 -
Altair Forum User said:
Hi Grant,
1.
thanks again for the complex answer, and Yes I now find Calculated Fields very helpful. I actually did some testing today and the RegexExtract is fantastic.
However, taking a step back and looking at my example from the beginning, I am not sure if it is possible to instruct the function to search for the last matched given pattern in a string.
Or is it?
RegexExtract(string,pattern[,N]) Scans the given string for matches to the given regular expression pattern. Returns the text of the Nth match found. If N is not given, this function returns the text of the first match found, if any.
I mean to set the function to reflect in field the last matched pattern. (However, I did create some 2 other columns with the 2nd and 3rd matched pattern, and then I created a reverse nested IF and I obtained the last matched pattern for each employee)
2.
Power Data Processing, do you recommend some good trainings, online resources for this? Stuff that could help me use Monarch better?
Thank you again for the attention and answers, it was a good week for Monarch thanks to you guys.
Have a nice weekend,
Radu
Hi Radu,
1.
I don't think the Regex functions would help with your trapping requirement.
However if, for example, you had to extract data as part of a large block of text but knew that in that block of text was a repeating pattern that could give you the separate records you require the RegexExtract function used to create multiple calculated fields might be a good approach.
Thin of something like PDF report where the internal structure of the PDF makes a clean formatted extraction impossible. Grabbing several lines a as block of text MIGHT (no guarantee) off a way to parse the data block and extract the individual records.
It could still be a difficult and time consuming task but if it was the only way it would still be worth considering.
The tools in the set of Functions and the operators that can be used with them are very powerful for text manipulation. Just think of them as a tool kit that can be combined in different ways to obtain the required output. If the task is complex break it down into easy steps using intermediate fields to create and test the transformations.
2. Historically the forums over the years have been a good source of information and support.
One or two of the most active advisors are either no longer with us or no longer active, sadly.
In recent product releases there have been significant new features, like the Regex functionality, that have probably not yet been fully explored by many users. Therefore the time is still right for asking the questions and building the knowledge in the community.
I am not aware of any packaged training that would address "Power" needs but I suspect that at the corporate level Datawatch can supply consultancy led training for specific needs. The requirements and the content of such training may vary somewhat depending in the industry.
More widely within the industry and across many products there may well be some guides and courses that could be purchased and that go deeper than "General Principles". However it's not an area that I have had that sort of depth of involvement with in recent times and across multiple industries.
I can only suggest that if you have a specific requirement, as here, raise the question and see what advice is offered.
Alternatively contact Datawatch through your company's sales or support channel and ask then what is available and how they would suggest you make use of it.
The final option is simply to enjoy using Monarch, keep and open and enquiring mind when you do and take the opportunity of a new challenge, when it arises, to discover some new features that you have not previously needed to see if they suggest a way that might provide new insights.
Soon you will be putting this collected knowledge together and coming up with new ways to approach challenges and you will become an expert through practical experience.
You might also be able to find a few people who are in a position to off mentoring that is specific to your needs rather than "classroom" style training or training "manuals" and sometimes this may suit your purposes better on a case by case basis.
HTH.
Grant
0
