CountRegEx - script and chart function

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A tabled named Vehicles containing a list of vehicles, along with the condition and model year of each vehicle.

• For each record in Vehicles, a calculation to determine whether the year range for the vehicle meets the expected syntax. Valid year ranges include 1990s, 2000s, 2010s, and 2020s. This calculation is performed using the CountRegEx() function.

Load script

Vehicles:
Load CountRegEx(Model_YearRange, '(^19[0-9][0-9]s$)|(^20[0-9][0-9]s$)') as Valid_Year_Data,
* Inline [
Vehicle_ID, Condition, Model_YearRange
1, Good,20100s
2,Fair,2020s
3, Poor,2020s
4, Good,210s
5, Excellent,2000s
6, Excellent,2020s
7, Excellent,202s
8, Good,2010s
9, Poor,2010ss
10, Poor,1990s
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Vehicle_ID

• Valid_Year_Data

You can also add a KPI to the sheet, and add this measure:

Sum(Valid_Year_Data)

This chart shows the total number of records with valid Model_YearRange values.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A tabled named Employees containing data about a company's employees. All email address for each employee are contained within a single string.

• The creation of a field, NoOfEmails, to calculate how many email addresses are contained within the input string for each employee.

Load script

Employees:

Load CountRegExI(Emails, '@[a-zA-Z0-9.-]*\.?[a-zA-Z0-9]*\.com') as NoOfEmails,
* Inline `
ID	Emails
1	john_doe@test.com john_michael_doe@example.com j.doe123@example123.com
2	jane_doe@test.com
3	Michael_Andrews@test.com Michael_John_Andrews@example.com
4	charlie_kent@support.test.com charlie_peter_kent@support.example.com cp.kent.1985@test123.com c_p_kent@test123.com
5	frances_johnson@test.com frances_johnson@example.com
6	rebecca_smith@hr.test.com
` (delimiter is '\t');

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• ID

• Emails

• NoOfEmails

Overview

Open the Data load editor and add the following load script to a new section.

The load script contains the text from a number of corporate correspondences that were sent out to employees of an organization. The data has already been cleansed of any personal identifiable information (PII), other than the company name and the email address that sent each correspondence. The body of each message has been collapsed into a single continuous string. In other words, all formatting and newline characters have been removed.

Our requirements:

• Identify which messages contain a string that includes the email address TestCompanyNameCorrespondence@test.com, along with a specific introduction to this address that indicates that it was the sender.

• We want flexibility in which introductions to identify, but also to still have some definite restrictions. The introductions Sender and Sent by are both considered valid. To achieve this goal, regex patterns can be used.

• Case sensitivity of email addresses is not a factor. Therefore, the search should be case-insensitive.

Load script

Correspondences:
Load * Inline [
ID|MessageBody
1|Sent by-- TestCompanyNameCorrespondence@test.com Good morning TestCompanyName team! I hope you are all well. I am just reaching out about the fabulous benefits package we launched just year. Our goal is to take a poll of what you all think. Can you please rate the new options from 1-10 in a response to this email, by this Tuesday? Thank you! 
2|Sender: TestCompanyNameIT@test.com Did you know you can access our IT portal anytime, from any of your company devices? It's true! Reach out to your team lead to learn more.
3|Sender: TESTCOMPANYNAMECORRESPONDENCE@test.COM URGENT: This is a message to inform employees of an ongoing issue with our local branch. The branch will be closed until further notice. Thank you
4|Sender: TestCompanyNameVolunteering@test.com Dear team! We are looking for volunteers to help with this year's charity event! We need 40 volunteers to help with the event. If you are interested, please send an email to your direct supervisor. Thanks, TestCompanyName team members, for helping make the world a better place!
5|Sender is TestCompanyNameCorrespondence@test.com Hi folks, it's time to announce the annual TestCompanyName staff party! The party will be taking place at the office this year. Please make sure to RSVP, and we hope to see you there! Sincerely, the TestCompanyName management team
] (delimiter is |);

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• ID

• MessageBody

Add the following measure:

=CountRegExI(MessageBody,'(Sender.*TestCompanyNameCorrespondence@test.com)|(Sent by.*TestCompanyNameCorrespondence@test.com)')

In the measure properties, set Totals function to Sum. This configures the totals row at the top of the table to count the total number of records that contribute to the count.

The measure counts the number of matches for the specified regex pattern within the message content. In the regex pattern, .* indicates any number of any character except newline characters. The pattern also accounts for variation in how the email address is introduced: both Sender and Sent by are valid matches. The CountRegExI() variant of the function ensures case-insensitive searches.

Across the five messages, there are three that match the specified regex pattern. Each message contains one match.