Range validation

Goat movie ratings

It's Wilde Week in goatville. The Wildes are like the Oscars, but for goat movies.

You have a worksheet like this:

The data starts in row 9. Each record has four fields:

• Movie title
• Genre (romance, action, or scifi)
• Average critic rating, from 1 to 5
• Average viewer rating, from 1 to 5

The data is not reliable, unfortunately. You can see a few errors in the screen shot.

You want to compute the stuff in rows 4 to 6:

• Number of valid records
• Number of invalid records
• Number of records where the viewer rating is higher than the critic rating.

You can download a solution.

Pattern

In the previous lesson on normalization, the pattern was something like this:

Let's add one more line. It will be the final shiny on this pattern.

So, after we normalize the fields in a record, we'll check them for errors.

​M0000t!

All but validation

Here's the code, except for the validation.

There's a For loop to run through the data:

Inside the loop, the first thing is to grab the fields for the current record. Here's the pseudocode:

Here's the real code.

The next step is to normalize.

The code:

The last step is processing.

The code:

The flag pattern for validation

Let's add the validation. We have three fields to check:

• Genre (romance, action, or scifi)
• Average critic rating, from 1 to 5
• Average viewer rating, from 1 to 5

We want to do the processing only if all of the fields have valid data. If there's an error in any field of a record, we want to ignore the record.

For that, we can use the flag pattern again.

Here's what the code will be like:

If any of the fields are bad, then tDataOk will be "no" by the time we get to line 11: If tDataOk = "yes" Then.

Validating genre

Genre is a string field with three valid values. Here's code that will validate it:

Ray

What if the genre is "Romance" instead of "romance". That If won't pick it up.

Georgina

We already normalized the data, so "Romance" will have been converted to "romance".

• tGenre = LCase(Trim(tGenre))

Validating ratings

Here's code to validate the critic rating:

First, we put the field's value into a string variable (line 1). If we put it straight into a numeric variable, the program will crash if the cell contains something like "fish".

Then we check whether it's numeric (line 5). If it isn't, set the flag, and that's it.

If tCriticRatingIn is a number, then copy it to the numeric variable sCriticRating (line 11). Then do a range check.

All the code

Let's put everything together. Check that this makes sense.

Check out lines 51 and 52. They let you count the number of invalid records.

Now you know how to analyze a Range.

M0000t! If you understood everything here, give yourself a pat on the back. This is getting to be real data analysis.

​M0000ty, m0000ty, m0000t, m0000t!