Kirix Strata Blog

In part 1 of this series, we looked at the basics of using calculated fields, including referencing fields and using functions.

In this installment, we'll look at how you can use calculated fields to extract data values from a column with “dirty” data. This happens more than one might think: if you've ever "cleaned" a list of contact information or migrated databases, you know that you often have to take a column and break it up into multiple parts. Calculated fields make this task very easy.

Let's look at an example. Suppose we have a table that contains raw contact information smashed together in a single field:

Let's extract each of these parts — first name, last name and email address — from the single column into multiple columns. We'll do this by using a calculated field with a function that parses the string, based on a delimiter (such as a space), and returns the relevant portion of the string. (more…)

Calculated fields are one of the cornerstones of data analysis. It's just one of those tools that you end up using again and again. And, if you can master the functions and formulas and make them do your bidding, you'll be well on your way to becoming a data Jedi. This article begins a multi-part blog series on using calculated fields in your projects.

Calculated fields are nothing more than a way to apply a formula to existing columns or fields. (As a quick aside, spreadsheets use the terms row/column, whereas databases use the terms record/field — there are some technical differences, but for our purposes here, they are very similar). A poor man's calculated field is found in a spreadsheet; you create a formula in a blank cell and then drag the cell down to apply it to the other rows. The more traditional calculated field is found in the database world. Here you create a new field that encapsulates a calculation and then you run it across the entire table.

With Kirix Strata, you get a calculated field like that found in the database world, but with the dynamic/instant properties you get in the spreadsheet world. So, when you create a calculated field in Strata, you get a calculation that applies to the entire data set instantly — whether its 10 rows or 10 million. In addition, these fields behave like regular ol' fields, so you can reference them in other calculated fields or use them in your sorts, filters, groups, relationships, queries and other operations. Of course, the actual data can't be edited like in a regular field, but the underlying formula can be tweaked — with the results showing up in your view in real-time.

Let's create some simple examples to show how calculated fields work. We'll look at the historic stock prices of Apple Computer (to try this yourself, just click the “Download to Spreadsheet” at the bottom of the web page and it will open up into a table in Strata):

(more…)

We had a really good question come up in our forums regarding Strata's ability to replace values within cells.

Bottom line, if you have a value in one field and want to auto-replace it with a different value, you can easily do this using the Update Records tool (Data > Update Records…). In a nutshell, it works like this:

1. Choose the field you want to update.
2. Choose the new value you want to update with.
3. Create the logic to let Strata know when to update a cell and when not to update a cell.

Both #2 and #3 enable you to get pretty complex, since you can use formulas in both of these areas. The “update with” (#2) area can be particularly tricky, since it gives you the ability to transform values on the fly.

However, it had been a while since I'd personally gone through the various options one can use to replace records, but as I did, I quickly realized our documentation wasn't comprehensive enough.

So, I added the “replace” rules to the post here.

Hope this helps. Now, if I only could figure out a way to replace Friday with Saturday… :) Hope everyone has a good weekend!

P.S. Got a question about Strata that you've been curious about? Please post a note on the support forums and we'll be happy to help!

We're just warming up the Strata blog right now, but we going to be adding various tips and tricks on how to use the software more effectively. One aspect of this will be to discuss general support questions we receive that have relevance to many users.

So, along those lines, we received a question the other day about concatenating fields together into a single string. Excel has a CONCATENATE function that will let you take two non-numeric columns and place them together. Here's an example from the Excel help manual:

CONCATENATE("Total ", "Value") equals "Total Value"

Strata makes this even easier, since no function is required to concatenate or join strings together. You simply need to add them together:

"Total " + "Value"  equals "Total Value"

So, say you had a table with a first name field (”firstname”) and a last name field (”lastname”) and wanted to put these together. You would insert a new calculated field and enter the following formula:

firstname + lastname

So, if a record had “John” in the firstname field and “Smith” in the lastname field, you would get a result of:

JohnSmith

The spacing is obviously problematic here, so we just need to add in a space for formatting purposes:

firstname + " " + lastname

which would result in:

John Smith

So, to sum up, no concatenate function is necessary when using Strata… just add your strings together.