Kirix Strata Blog

In the last post, we talked about Julian dates and Unix Timestamp conversion. We recently received a support request regarding another type of date conversion and, since it might be applicable to others, we thought it would be a good idea to show how you can convert it in Strata. The format is C-YY-DDD, which is used by JD Edwards software: the century after 1900, the year in that century and the Julian date within that year. Here are a few examples:

105031 - January 31, 2005
107263 - September 20, 2007
98129 - May 9, 1998

To convert the dates we first want to get the string into the proper year and then we'll add the Julian date to get the proper day/month (which will also take into account leap years). So, let's take the example “107263″ (we'll assume that it is in Numeric format in your field). Here is the full expression you can use in a calculated field:

date(1900+(field1/1000),1,1)+val(right(str(field1),3))-1

Getting the Year

date(1900+(field/1000),1,1)

The first part of the formula takes the string and divides by 1000, which, when rounded, will provide the first three digits (”107). It then adds 1900 to the 107 to give us “2007″. Then the normal date conversion applies and we end up with “01/01/2007.”

Getting the Day and Month

val(right(str(field1),3))-1

Here we are adding the Julian days for the year to the date we created above “01/01/2007″ — so, if we added “1″ to this date, we would get 01/02/2007, and so on. Here we are using the RIGHT() function to pull out the 3 digits on the right side of the field, in this case “263.” Because it was numeric, we needed to first convert it to a string using the STR() function and then we converted the result back to a numeric value using the VAL() function. This gives us “09/21/2007.” Because we started at “1″ (January 1st) instead of “0″, we simply need to subtract 1 from our date to obtain the correct date of “09/20/2007.”

NOTE: If your JDE date was a character field instead of a numeric field to begin with, you could change the structure or just convert manually in your expression with the VAL() function as follows:

date(1900+(val(field3)/1000),1,1)+val(right(str(val(field3)),3))-1

If anyone has other date conversions they're having trouble with, please let us know and we'll see if we can help.

Converting date formats can be a tricky thing. Two of the more cryptic formats that come up in our support questions relate to Julian dates and Unix timestamps. Here's how you convert them in Strata:

Julian Dates

A Julian date is the number of days since January 1, 4713 BC GMT. To convert a Julian date to the current date, you can simply use DATE(0,0,0) + <number>, where <number> is the Julian date. For for example, if the number appears as “2454917″, the formula in Strata would be:

date(0,0,0) + 2454917 [the result would be March, 26, 2009]

Unix Timestamps

A unix timestamp is the number of seconds since 1970. To convert a unix timestamp to the current date, use DATE(<number>), where <number> is the number of milliseconds since 1970. If the timestamp is given in seconds, multiply by 1000: DATE(<number>*1000). So, for example, if you the number appears as “1237657172″, the formula in Strata would be:

date(1238088021 * 1000) [the result would be March, 26, 2009]

As a side note, sometimes one might mistake a Julian or Unix date for a different custom format. For instance, dates could be stored in the format YYDDD or YYYYDDD where YY/YYYY is the year, and DDD is the number of days since the beginning of that year. So 2001003 would be January 3, 2001

For further information on Strata date conversion, please see this help page.

Strata can work directly with fixed-length or delimited text files. Let's say we need to change the structure of a text-delimited file — say, making a field with smaller to truncate unnecessary values and reduce our files size. We can do this using two methods, either by manually changing our values or by changing the values programmatically prior to import. (As an example, we may have wanted to reduce our web log field width size in this example).

(more…)

Continuing with our video tutorial series, here's one on creating calculations and using formulas. This one briefly looks at how to create calculated fields, but quickly delves into more complex formulas and functions. This one would be worth watching, even if you're an expert — it may give you a few new ideas.

Just wanted to get out a couple more tutorial blogs to everyone before the final page gets published:

Sorting:

Filtering:

Enjoy!

We're in the midst of putting together video tutorials that cover each of Strata's main features. I figure I'd leak some of these ahead of time on this blog, as many may find them useful.

This first video is sort of a basic “rules of the road” for getting around the Strata interface. It's a little over 3 minutes long and covers things like using tabs, panels, various views, and controls in the table view like moving columns and editing.

If you're an expert Strata user, you've probably already got these bases covered. However, there's a good shot that many people will probably pick up at least one thing new.

More rogue tutorial videos to be posted soon…

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!

It's been almost one year since we released the beta version of Kirix Strata to the public. During that beta cycle, we provided several videos and screencasts via our blog to emphasize different things the software could do.

Thankfully, even though the videos show the beta version in action, almost all of the content is extremely relevant for the final version of Strata as well. The only variance really has to do with the user interface; we ended up moving around icons and toolbars and menu items quite a bit until we got something that seemed to work best. Oh, and you may see the original Strata logo that we threw together for the beta.

So, maybe you can consider this blog post your Tivo or on-demand video page for “Season 1″ of Kirix Strata. Here are the five links, with details and highlights of each one below:

• Access and Manipulate Data from the Web
• Identify Embedded Search Terms within Apache Web Logs
• Manipulate Call Center Data and Create a Mapping Visualization
• Features Overview: Data Bookmarks, Quick Find & Quick Filter and Report Writer
• Combine Table from MySQL Database and HTML Table from the Web, then Create a Report

(more…)

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.