Dealing with Nulls in Veera Transform Formulas

This next post comes from Jeff Fleischer, our Director of Client Operations, support wiz, and analyst extraordinaire: 

Working out the logic of a new variable you want to create with a TRANSFORM node can be challenging. But when missing data ("nulls") get into the mix, it can be especially confusing and frustrating. For example, if you'd written the conditional formula...

               IF ([A]='Freshman', 'UG', 'Grad')

...and some of the fields under column [A] were null, you would get nulls as an output for those rows rather than the desired 'Grad'. This is because trying to equate something with "nothing" confuses Veera as to what you would really want as a result. So here are some suggestions on how best to deal with those gaps and still get to the outcome you need...

1. 
The most obvious way to deal with gaps in data is to replace them with something. This may not always be desirable, but when it is, using a CLEANSE ahead of your TRANSFORM is your best bet. Select the "Is Missing" operator and use Alt-Left Mouse to select all the columns that need their data fields filled in with that new value, like 'unknown'. 

Of course, you could instead place a CLEANSE after your TRANSFORM, using it to fill in any missing values appearing in the new column. 

2.
If filling in those data holes using a cleanse is not preferable, maybe just a temporary patch will do. Look for the "Treat Missings in Formula as Zeros" checkbox just above the "New Variable Name" field in the TRANSFORM. Just as the name suggests, this will temporarily replace any missing data with a zero, allowing most operations to function. Be careful, though, if the column you're evaluating already contains zeros - the output may not be what you intended!

3.
If even temporarily replacing nulls with something else isn't an option, then change your TRANSFORM formula to deal with them ahead of everything else. To do this, you'll likely need to use one of two built-in Veera functions - IS NULL or IS NOT NULL. We might change our example to include another condition, such as...

IF ([A] IS NULL, 'Withdrawn', 
IF ([A]='Freshman', 'UG', 'Grad'))

The idea here is to catch any nulls before they affect the rest of the logic by putting that condition first. 

4.
Finally, another (if more specialized) option might be to use the "Missings:" TRANSFORM feature. Unlike the "Treat Missings in Formula as Zeros" checkbox, this control changes nulls that appear as the final result of a formula. The replacement options offered by this feature are limited (0 or 1), but it may be an easy way to fix a problem with absent data appearing in a new numeric field. 

-Jeff Fleischer

Creating Variables: Fiscal Year

                For those of you whose fiscal year is different from the calendar year, having a Fiscal Year variable can be a huge timesaver, which is why we've chosen it as the next entry in our Creating Variables series. Filtering and sorting on this variable make it easy to compare things like gifts on a fiscal year to fiscal year basis, as well as easily focus on one or more years of interest. Fortunately, creating this variable is easy by following these steps in Veera:

The first step is to hook your dataset to a transform node:

Once in the transform, select the date variable (in the form DD/MM/YY) you’d like to extract fiscal year from. Next, click on the “IF” button (the top button on the right-hand side) to generate an ‘if’ equation. In the Enter A Formula window, we’ll want to edit the auto-generated equation so it reads:

IF(month(A)>=7,year(A)+1,year(A))

where A is the date field that you’re extracting fiscal year from. This example is assuming a July 1 fiscal year start, which is why we used the number 7 (feel free to edit accordingly). Be sure to name the new variable and select “text” from the Result Type list before saving.

The formula is saying that if the month of the date field falls after the beginning of the new fiscal year, then set the fiscal year to the newer fiscal year (which is the year after A because the fiscal year will end in that next year). Otherwise, we’re setting the fiscal year equal to the year of the date field (because the fiscal year ends in that year).

Finito!

-Caitlin Garrett, Statistical Analyst at Rapid Insight

Customer Tips From... Dan Wilson (Muskingum University)

Our next set of customer tips comes from Dan Wilson, Registrar at Muskingum University. Dan typically uses Veera for repetitive and/or complex reports, including multi-year enrollment history by date, historical majors and minors (by year and department), and IPEDS reporting. Here are his tips:

1. It is important to remember the merge characteristics (all from a, all from b, all from both, only matching, etc.) so the last thing I do in developing any report is to verify each of these. 

2. While Veera's CrossTab feature is quite useful, I find it easier and more familiar to output my results to a target excel file, and then have another excel spreadsheet with my pivot table that has all of the formatting and other features set up. That way I can update the data file without overwriting my formatted "results" file. The same can be done with separate sheets in a file, but some of my reporting files pull data from different queries and Veera reports. For those reports I can run data from several sources, then open up my main file and hit "refresh". 

3. For those instances when a transform looks like a computer program, I'll break it into smaller bits and spread it out over several nodes. This allows me to test smaller chunks of the function at a time and locate any errant code prior to needing valium. (Editor's note: using the de-bugger in the Transform node can also help to find errors quickly!)

...Have tips of your own? Email them to caitlin.garrett@rapidinsightinc.com!

Introducing the Rapid Insight Collaborative Cloud

With the new version 4.1 of Veera comes the Rapid Insight Collaborative Cloud. The Collaborative Cloud allows Veera users to share and collaborate on any analytic processes that they develop; these processes can be utilized, discussed, and enhanced by anyone in the RI user community. We hope that the cloud will increase efficiency by allowing users to share resources and ideas in real time and draw upon the knowledge of their peers whenever they begin a new analytic challenge.

Accessing the Cloud
To access the Collaborative Cloud, click on ‘RI CC’ on the title bar and select “Browse RI Collaborative Cloud” as shown: 

Downloading from the Cloud

Once in the cloud, you have the ability to search for Veera jobs, transform operations, and chart styles to download.  You can choose to search these by contributor. You also have the ability to order search results by contribution date or type, number of downloads, number of comments, name, date modified, or contributor.

To get details on an item or select an item to download, simply double-click on its title. To view or leave comments, double-click on the “# Comments’ field.

If you choose to download a transform operation or chart style, Veera will direct you to save to a folder outside of the Veera program, similar to the way you would save any outputs from Veera jobs. If you choose to download a job from the Collaborative Cloud, Veera will place that job in an automatically created folder titled ‘From RI Collaborative Cloud’, which you’ll see along with your other folders in the Workspace tab:

You can access any of the jobs you download from the cloud by navigating to this folder. 

Uploading to the Cloud
Right-click on the item to be uploaded (job, transform, chart style, or add-in) and select “Contribute [item type] to RI Collaborative Cloud”. Once selected, the Contribute to Rapid Insight Collaborative Cloud window will open with the original name of the item entered already at the top. 

Before submitting by clicking “Contribute”, the user must enter a unique name, a description of the item, and check the Terms & Conditions box before the item can be contributed. Additional files (like related sample data) can also be contributed as part of the submission.

Other Ways to Download
You can quickly download jobs from the Collaborative Cloud by right-clicking in the “Jobs” area of the Workspace tab and selecting “Get Job from RI Collaborative Cloud”:

You can also import transforms from within a Transform node. To do so, right-click on the white area in the “Transform Operations” window at the bottom of the screen and select ‘Get transform from RI Collaborative Cloud”.

To import a chart style from within the Chart Data node, click on the ellipsis to the right of the “Chart Style” drop-down menu. In that window, right-click and select “Get style from RI Collaborative Cloud” as shown below. 

-Caitlin Garrett, Statistical Analyst at Rapid Insight

Updated Features in Veera 4.1

The new and improved version 4.1 of Veera has quite a few updates. Here are 12 of the biggest changes you'll see when you update your version:

1. Transforms can now be saved for future use. If you have a useful formula or transform that you’d like to save to apply to multiple datasets, this means you can save the whole transform and export from or import into other jobs as needed.

2. Cache node now has a ‘view data’ option. So, once a cache is full, you can see what the data looks like at that point in a job, rather than having to check it out an output.

3. When using a Combine Input node, you can now create a “file date created” column and a “file modified column” to better track the dates associated with each of the files you’re importing.

4. Job and data view windows can now be un-docked and float outside of the main Veera window.

5. Sampling node now has ‘range’ option. Instead of having to sample records from the top or bottom of a dataset (if you didn’t want to sample randomly), you can now choose which range of records you’d like to sample from.

6. Sampling node is also parameterized. You can set a parameter to prompt you for the number of records to sample in each sample node.

7. When restoring connections, Veera will now Auto-backup in case anything was to go wrong with the restoration process.

8. A “busy” window will now show when Veera Client is connecting with the server.

9. Setting run order can now be done from a list rather than having to click on each output in the order you’d like them to run.

10. The Quantile node now allows you to choose whether to sort values in a descending or ascending order.

11. Fields on an Excel Output file can now be parameterized. So, if you’re running jobs with a particular parameter, this parameter can now be included as part of the outputted field names.

12. The new Variable Reduction node has now been implemented. When available, Variable Reduction will filter down the variables in your dataset so that you’re only looking at variables that are statistically significantly related to your chosen Y-variable from that point forward. 

...As always, we update our packages based on our customers' ideas and needs. If you have any feedback or ideas regarding either of our packages, we encourage you to leave them in the comments or email us directly. We'll make sure your ideas find their way to the right person.  


-Caitlin Garrett, Statistical Analyst