Go to Top

Capitalize Analytics Blog

Alteryx Solution for Updating Workers’ Compensation Code on Employee Records

There are many times when we need to get large amounts of data into a system, but the front end of the application doesn’t provide a way to do it quickly. This creates a dilemma of how to get hundreds or thousands of items loaded, without doing it by hand, which could take days. Alteryx is a solution that makes data movement, blending, and loading quick and easy. It is also a solution that requires ZERO SQL, or other coding skills so more users can be part of the process.

During a new implementation of PowerSchool’s eFinancePLUS, a district wanted to use the system’s ability to calculate the workers’ compensation premiums during the pay run processing. They created the appropriate codes and assigned them to the right job classes for the processing to occur. However, the codes were not able to be added en masse to individual employee records for later use in running informational reports.

Because the district had added the codes to employee records in a spreadsheet, an Alteryx workflow was created to update the appropriate table in the database with the assigned codes. An explanation of the workflow process follows.

The actual SQL server/database connection information is hard-coded in the workflow.

The demographic conversion spreadsheet with employee information has many columns, but it must have the [EMPLOYEE NUMBER] and [WORKCOMP] columns. It can only contain one sheet.

Since the directory path and name of the spreadsheet could vary, a ‘File Browse’ Interface Tool is used to ask the user to locate the desired file. Text is displayed that instructs the user on what to do.

 

An ‘Action’ Interface Tool is used to tell Alteryx what to do with the information. In this case, the action type is ‘Update Input Data Tool (Default)’ and it is required.

 

An ‘Input Data’ tool is used to define information about the spreadsheet data. The default name assigned in the configuration will be replaced with the file name selected by the user in the previous step.

 

A ‘Select’ tool is used to keep only the employee number and workers comp code from all the columns in the spreadsheet. The Size field of the workers comp code was changed from the default setting of 255 to 4, as that’s how it’s defined in the database table.

 

A ‘Filter’ tool is used with a Custom Filter to keep only the rows where both the employee number and the workers comp code are not Null. The expression used is:
!IsNull([EMPLOYEE NUMBER]) and !IsNull([WORKCOMP])

 

This tool has two outputs – T(rue) and F(alse). Nothing is done with the records that don’t meet the criteria and that portion of the workflow ends. The rows that meet the condition are passed to the ‘Join’ Tool.

An ‘Input Data’ tool is used to connect to the server and access the reference table where the valid workers’ comp codes are assigned to job classes. A SQL statement is used to accomplish this and is entered in the ‘Table or Query’ configuration option:
Select distinct wkr_comp From clstable

A list of distinct values is returned as the same code could be assigned to more than one job class. The resulting rows are passed to the ‘Join’ Tool.
A ‘Join’ tool combines the output from the previous steps and has three possible outputs. ‘Join by Specific Fields’ is selected in the configuration and the common field from the database table is associated to the common field from the employee information (workers’ comp code).

 

The ‘Left’ output contains the database reference table values that don’t match any of the employee records. Nothing is done with these records and this portion of the workflow ends.

The ‘Right’ output contains the employee records with a workers’ comp code that has not been assigned to any job class records. Since pay run processing is based on values in the job class table, if codes assigned to employees are NOT assigned to any job class records, no processing occurs and having employees assigned to these codes is irrelevant. These records will be displayed for the user to review.

The ‘Join’ output contains the employee records with a workers’ comp code that has been assigned to at least one job class. These records can be used to update the database table.

An ‘Output Data’ tool is used to update the database table. The server information and destination table are hard-coded in the configuration. The ‘Output Options’ value is set to ‘Update: Warn on Update Failure’ and the ‘Append Field Map’ uses a ‘Custom Mapping’ to assign the workflow fields to the corresponding table fields, as shown:

A ‘Sort’ tool is used to sort the records needing review by the user since their workers’ comp codes didn’t exist in the job class table. They are sorted by workers’ comp code then employee number.

 

A ‘Table’ tool is used to set up the layout of the records for use in a report. ‘Table Mode’ is set to Basic in the configuration and ‘Show Column Headings’ is selected. The workers’ comp code is displayed first, then the employee number.

 

A ‘Report Header’ tool is used to create a title for the report. The date and time is included in the header for this report, but no logo is added.

 

A ‘Layout’ tool is used to order the output on the report. The ‘Layout Mode’ is set to ‘Each Individual Record’ in the configuration and the ‘Per Row Configuration’ specifies the ‘Header’ tool is displayed before the ‘Table’ tool.

 

A ‘Folder Browse’ tool asks the user where the report should be saved. Text is displayed that instructs the user on what to do.

An ‘Action’ Interface Tool is used to tell Alteryx what to do with the information. In this case, the action type is ‘Update Value (Default)’. The ‘Replace a specific string’ option on the configuration is checked and the file path of the default file name for the report is entered in the text box. This information will be replaced by what the user has selected.

 

 

A ‘Render’ tool is used to create the PDF report of the records needing review by the user. The ‘Output Mode’ in the configuration is set to ‘Choose a Specific Output File’ and a default file name including a directory path is entered. The directory path will be replaced by what the user selected in the previous step,                                                       but the file name entered will be used as-is.

 

Because Interface Tools are used, Alteryx automatically sets the workflow configuration to ‘Analytic App’. (Clicking anywhere on the canvas – not a tool – displays the workflow configuration on the left.)

When the app is run by the user, a dialog box displays:

The user selects both the spreadsheet of employee information to import as well as the directory to save the PDF report in.

Clicking the ‘Finish’ button runs the workflow. If there are records needing review by the user, this displays when the workflow ends:

Clicking ‘OK’ opens the report. The user can also click the ‘Show Output Log’ link to see the number of records processed at each step.

If there are no records needing review by the user, this displays when the workflow ends:

The user is returned to the main dialog screen where they can run the workflow again with a different input file if desired, or click the ‘Exit’ button to close the app.

Using Alteryx to create a workflow allows the user to update data in a table without knowing any SQL. They can run the process multiple times if they decide to change or add codes after the initial run. Built-in data checking can also prevent errors in data later.

For additional information, please contact us at marketing@capitalizeconsulting.com!

Vendor Spotlight Webinar: DataRobot

 

 

Automated Machine Learning for Predictive Modeling

Originally Recorded on Wednesday, July 11, 2018 @ 12:00 pm CDT

What should you be doing with AI and Machine Learning?

How are your competitors leveraging predictive models?

According to Forrester Research, AI-driven companies will take $1.2 trillion from competitors by 2020. In the age of artificial intelligence (AI) and big data, organizations must embrace and leverage new automated machine learning technologies to build a competitive advantage and succeed.

In this vendor spotlight webinar, attendees will learn:

  • How machine learning and AI are transforming the way business is done
  • How to implement machine learning initiatives without hiring a large team of difficult-to-find data scientists
  • The basics of automated machine learning and how it enables organizations to make better, faster decisions that result in tangible business value
  • A demo of the DataRobot automated machine learning platform

To keep from falling behind, watch our webinar Automated Machine Learning for Predictive Modeling.

Watch Now

ThoughtSpot 4.5 Now Available!

This release lets you view trending and popular content on a new Smart Homepage, compare measures with a single keyword, perform advanced time-series analysis, and a whole lot more.

Check out some of the highlights of this release:

Smart Homepage
Easily access trending, popular content

Comparison Analysis
Compare measures with a single key word

Enhanced Time Series Analysis

Pivot Tables & Localization
Country Maps, Pivot Table Formatted Reporting, Localization

 

There’s a whole lot more included in ThoughtSpot 4.5. See a demo today and experience the next generation analytics platform that lets you use search to analyze your data and get automated insights with a single click.

For more information, please contact marketing@capitalizeconsulting.com

Upcoming Webinar! Vendor Spotlight: Agile Upstream

Know More with AI: Let the Software Read Your Leases and Agreements

Wednesday, June 06, 2018, 12:00 pm – 1:00 pm CDT

Finding critical information in lease contracts is a tedious process and can be costly even if one single provision is missed.

Agile Upstream has developed an Artificial Intelligence platform providing E&P land departments the ability to quickly upload lease documents, extract critical data, and review and validate vital information. A process that typically takes months can be completed in days, allowing for decreased financial risk and liability, and improved operational efficiency.

Join Capitalize Analytics and Agile Upstream to hear how Agile is transforming the way information is managed and leveraged in upstream and midstream.

Ideal for: Land professionals, Technology and Strategy Experts at E&P and midstream companies

Key takeaways: Hear how cutting-edge technology is quickly surfacing the critical data in contracts and lease documents needed to make accurate strategic decisions.

After registering, you will receive a confirmation email containing information about joining the webinar.

Can’t make it? Still register and we’ll send you the recording!

Register

NAPAC 2018

This week, Capitalize will attend and exhibit at NAPAC, the North American Petroleum Accounting Conference, Thursday, May 17 & Friday, May 18 in Dallas, Texas at the Westin Galleria.

We look forward to seeing old friends and introducing ourselves to new ones!

Stop by and see us!

 

O&G Tax Calculation: A Cognos Success Story

When it comes to taxes, the US government can be particular about how they want taxes prepared and calculated. In the state of Louisiana, taxes are applied to the physical well’s volume and sales allocated, and not to the tract. A tract is an ownership percentage of one-to-many physical wells.

The problem: Our client has an allocation system that applied taxes to the tracts and not to the physical wells. The client attempted to take those tract results back into the physical wells, which was proved to be difficult and time-consuming. Complicating matters further, two sets of physical wells were added together and then split 25/75 based on contract terms, and some were Take-in-Kind contracts. This led to too many errors in taxes paid.

The client also had to deal with paying on six different types of taxes. Three of them were volumetric-based, while the remaining three were based on sales value. Depending on the exemption status of the physical well, applying three tiers of rates depending on exemption certificate was also needed. This led to 12 different tax entries for each owner tract settled.

The solution: The allocation system that the client used already contained all the information we needed to produce the required result: physical wells’ volume, the rates, liquid allocation, and meter type to determine which rate to use.

We created a report that would calculate the information we needed and be easily loaded via template into the client’s database. This report calculated the taxable volume for taxes paid on volume per physical well and on calculated sales condensate values. Each meter was broken out by the six taxes charged in the report to be uploaded. Unlike the tracts, physical wells could only have six different charges of tax on Gas and Liquids, while tracts could have up to 18.

We created another report to help the tax department determine which physical meters needed to apply for tax exemption or reduction based on flow hours. Lower flow hours allowed them to apply for a reduction in tax rates in a timelier manner.

The Conclusion: These new reports helped the tax department process tax filings faster and created a more efficient means of communication between the tax department and accounting pertaining to the application of exemption to physical wells. The solution also prompted the client to invest resources in accounting software that mimics the report function in the application itself, by calculating the taxes before dividing them into tracts.

For additional information, please contact us at marketing@capitalizeconsulting.com!

Upcoming Higher Education Webinar: Five Cognos Tips Every Banner Client Needs to Know!

If your institution has Banner and you want to take full advantage of Cognos, then this is the webinar for you!

Capitalize Analytics is an IBM premier business partner focused on helping higher education get real meaning from their data. We understand today’s institutions are pulled in multiple directions and challenged to meet higher expectations with fewer resources.

In this webinar, we will demonstrate:

1. Dynamic Dashboarding: Explore where your departments are spending their budgets.
2. Geographic Dashboarding: Use maps to compare the high school performance of admitted      students to the campus average.
3. Drill Through Dashboard to Reports: Discover student retention demographics by course        and department with drill through reports.
4. Communicating Information through Bursting: Burst your budget reports so college                deans know where they stand financially.
5. Getting Notified about Changes in Status from Event Studio: Notify your financial aid            students when they fail multiple classes.

Attend our webinar and let us show how you can get the most out of Cognos for Banner!

Register

After registering, you will receive a confirmation email containing information about joining the webinar.

Upcoming Live Webinar! Revolutionize Analytics for the Oil, Gas, and Energy Industries

Thursday, May 10th , 2018 at 1:00 pm CDT

Join Alteryx and Capitalize Analytics as we take a deep dive into how self-service analytics, combined with a complete end-to-end platform, can revolutionize how people in the Oil, Gas, and Energy industries can use data to gain valuable insights. Walk away knowing how the right platform can help you:

 

• Accurately anticipate energy demands, like peak usage time, so you – and the power grid – are never caught off guard

• Understand the root cause of failures to eliminate them in the future, or predict and address possible problems before they happen

• Closely watch your supply chain and easily keep track of all moving parts with automated reporting

 

 

 

 

With examples and use cases from companies like Shell Oil, this webinar will be useful for everyone from the citizen user to a professional proficient in R and python.

Register

Upcoming Webinar – ThoughtSpot: Stop building reports/dashboards and just “search” for answers!

Thursday, April 26th at 12:00 pm CT

Capitalize Analytics works with dozens of vendors to solve data challenges. Rarely are we excited or blown away by a technology. ThoughtSpot will be the answer to a lot of our clients’ data/reporting challenges.

Tableau, Qlik, Cognos, BusinessObjects, etc. are all great tools in the correct hands. The problem is that even the simplest tools are too complex for average users. ThoughtSpot changes this by allowing end users to “search” for answers just like they would in Google, Amazon, etc.

With ThoughtSpot you just search for “Sales merchant product weekly last 3 months” and immediately get the answer!

Gartner has recognized ThoughtSpot as an analytics visionary and the biggest mover on the Magic Quadrant. 

 

ThoughtSpot doesn’t replace your traditional BI strategy. Instead, it gives a new, non-technical audience the ability to truly “self-serve” for the first time.

 

Join Capitalize Analytics  to see how ThoughtSpot will have your company “searching” for answers more quickly than any other tool available!

 

Can’t make it? Still register and we’ll send you the recording!

 

Register

INDEX and MATCH Functions – Multiple Criteria VLOOKUPs

In an ideal world, we would always receive the data we need in exactly the format we need to have it in. However, in the real world, analysts are often given dumps of data from various sources that need to be consolidated into a useful report. Many times, this consolidation is done in Excel using the VLOOKUP function. A limit of the VLOOKUP function, however, is that it can only be used to join data on a single criterion. A less commonly used way to join the data together is through a combination of the INDEX AND MATCH functions. The INDEX/MATCH method can be used when we need to join the data on single or multiple criteria, making it more flexible than the VLOOKUP function.

Following is a case where we need to join data on multiple criteria. Suppose our company is a Natural Gas producer and uses its budgeted Ethane (C2) and Propane (C3) volumes to hedge against price fluctuations. We are asked to build a report showing the Actuals-to-Budget variance of our sales volumes by meter by month for our six (6) highest volume meters. We have built the structure of our report and updated the Budgeted volumes as well as the formulas to calculate the variances. All that we need to complete the report are the Actual volumes.

 Actuals to Budget Variance Report:

Actuals Data:

For the Actuals Data, we receive a dump of all NGL sales for all the company’s meters and all months.

To bring the Actuals data accurately into our report, we need to join the data on not only the Meter #, but also on the Product and the Month. A VLOOKUP would be able to join the data on any one of these criteria, but not all three. To join on all three criteria, we can use the Index/Match option.

First, let’s break down the Index function and Match functions separately.

 INDEX Function (array, row_num, [column_num]):

With the INDEX Function, we specify a range of data and a row and column number within that range (where the upper left cell of the range is in row 1 and column 1). The INDEX function will return the cell where the specified row and column numbers intersect.  For example, below we have set our range to be A1:E541 which is the entire set of our data dump. We then specify it to return the value in Row 3, Column 5. This value is equal to 530.

The INDEX function gives us a way to return an individual cell from a range. To make this function more valuable though, we need a way to find a specific row of data based on the data required in our report. To accomplish this, we can use the MATCH function.

 MATCH Function (lookup_value, lookup_array, [match_type]}:

The MATCH function allows us to enter the value (lookup_value) we want to join and the range (lookup_array) where we want to find that value. For example, below we told the MATCH function to find Meter # 12354 (lookup_value) within range A1:A541 (lookup_array). The function returns number 3 which is the row within the range where Meter # 12354 is first encountered.

Rather than returning the row number with the first occurrence of Meter #12354, suppose we want to return the row for Meter #12354 with product C3 in Production Date 03/01/2017. In other words, we need to return the row number base on multiple criteria.

We can accomplish this by using an Array formula. Specifically, we can use the “&” symbol to join multiple lookup_values and multiple lookup_arrays. One important caveat is that any time you create or edit an Array formula, you must type Ctlr+Shift+Enter in the formula bar to evaluate it. After typing Ctrl+Shift+Enter, curly brackets will appear on the outside of your formula. If you only type Enter after editing an Array formula, a #VALUE! Error will be returned.

Below is a screenshot showing the use of an Array Match formula to identify the desired row based on Multiple Criteria. Note the curly brackets on the outside indicating that it is an Array formula.

The formula returned row number 138. When we look, we can see that row 138 does in fact match all 3 of our criteria: Meter #=12354, Production Date=3/1/2017, and Product=C3.

Populating Actuals in our Report (INDEX/MATCH combination):

Now that we have a way to return a value from a row/column intersection (INDEX) and a way to identify specific rows of data (MATCH), we can combine the two functions to bring the Actuals data accurately into our report. Since our goal is to return the data within certain cells, we start with the INDEX function and specify the column array where the data we want to return is located. In this case, the data we want to return is Mbbls (thousands of barrels), and these values are in range AA4:AA544.

Next, we need to specify the row number for the INDEX function. Instead of specifying a specific number, we can nest an Array MATCH function inside the INDEX function to identify the row number based on our 3 criteria: Meter #, Product, and Month.

We use a combination of Relative and Absolute references ($ symbol) to make sure our formulas will reference the correct cells once we copy them.

Since we only have one column in the Array for our INDEX function, specifying the column number is optional. Here we will explicitly specify it equal to number 1.

We have entered all the arguments for the INDEX function, so we close the final parentheses and press Ctrl+Shift+Enter to evaluate the Array formula. We know we did this correctly because the curly braces appear around our formula.

The formula returned 2,422 MBBLs of C2 for Meter #12347 in January. Searching through our raw data, we can verify that the formula did return the desired value.

We can now simply copy our formulas through the rest of the Actuals columns, and our Actuals-to-Budget Variance report will be complete!

We see that through March, we have exceeded our C2 budgeted volumes on our highest volume meters, but we have fallen short on C3 volumes. The largest drivers of these variances are Meter 3 and Meter 6.

In conclusion, the major advantage of the INDEX/MATCH method is the ability to lookup values on multiple criteria. Using this method can be very helpful for quickly bringing raw data into monthly or ad hoc reports that have been built in Excel.

For additional information, please contact us at marketing@capitalizeconsulting.com!