Counting Topbox Ratings Across Multiple Brands

Consider a bank of ratings questions like the FavRat set in the Ruby Demo job.  The mission is to construct a variable which counts the number of brands with a top two box rating of 8, 9 or 10.  If any two of the brands have ratings of 8, 9 or 10, then output a 2, etc.

The trouble with this sort of scenario is: easy enough if just a few brands, like the FavRat example, but what happens if 300 brands accumulated over a fifteen year tracker?  You don’t want to be adding each new brand which comes along to variable or table specs manually – most tedious and error-prone. The ideal is a few lines of script which always work, and which never require modification in the event of new brand codes.

The first step is to repackage the individual brand variables (eg FavRat_1, _2 and _3) into a grid.  If you imported from SAV or SPS/ASC with blending, or MDD/DDF or other supporting format, then banks of rating variables should already be a grid. If not, to create a grid from a set of flat ratings, use eg

    DefGen "FavRat", "FavRat_$a($b)", "Favourability"
        AddLevel "a", "Brand",  "AidedBrandList", "*"
        AddLevel "b", "Rating", "FavRat_1",       "*"
    GenClose
    Construct "FavRat"

AidedBrandList is a codeframe only (no case data) which simply defines codes 1/3 as Brands X, Y and Z.  Any variable which defines the $a codes will do. (For details on DefGen syntax, and how the generators work, search the GUI and API Help files for “defgen” and “generator”.)

Either way, your FavRat grid case data should look like this:

The final output, then, should be 1 for cases 7/10;14 and 2 for cases 20/21, etc.

Given a grid representation, the next step is to construct a flat variable which lists the brands with a T3B rating.

    '' T3B as flat

    DefGen "FavRatT3B", "FavRat.Brand($a)&FavRat.Rating(8/10)", "Favorability T3Box"
        AddLevel "a", "FavRatT3B", "FavRat.Brand", "*" 
    GenClose
    Construct "FavRatT3B"

Showing against FavRat, FavRatT3B is:

If a ‘b’ is followed by 8, 9 or10, then the preceding ‘a’ brand code has been extracted as multi-response.

Now, finally, to count the number of values in FavRatT3B, and output as uncoded increments to FavRatT3BCVL:

    DefCon "FavRatT3BCVL", "Count of T3B Brands"
        AddInc "*", "FavRatT3B(*)", "cvl_FavRatT3B", ""
    ConClose
    Construct "FavRatT3BCVL"

Showing FavRatT3BCVL against its ancestors:

The FavRatT3B values are brand codes, and the FavRatT3BCVL values are the number of brand codes.

There’s only one problem: what if I want to count the zeroes, that is, output a zero to mean no T3B brands? FavRat is response-complete, but that’s often not the case, and we don’t want to mix up missing/NA/NoAnswer etc with respondents who gave valid but no T3B ratings.

There are two ways to achieve this:

1. Construct the opposite list to FavRatT3B, specified as

    DefGen "FavRatNT3B", "FavRat.Brand($a)&FavRat.Rating(1/7)", "Favorability No T3Box"
        AddLevel "a", "FavRatNT3B", "FavRat.Brand", "*" 
    GenClose
    Construct "FavRatNT3B"

Then expand the filter for FavRatT3BCVL to include FavRatNT3B respondents too:

    '' count the number of values in FavRatT3B if either FavRatT3B or FavRatNT3B has a value,
    '' ie, write a 0 for no T3B for any brand
    DefCon "FavRatT3BCVL", "Count of T3B Brands "
        AddInc "*", "FavRatT3B(*)|FavRatNT3B(*)", "cvl_FavRatT3B", ""
    ConClose
    Construct "FavRatT3BCVL"

The final output, against ancestors, is:

Case#1 has no T3B ratings, so the count is 0. Case#7 has one T3B brand, so the count is 1, etc.

You would use this approach only if FavRatNT3B is a useful variable in its own right.

2. Use the FavRat grid instead as the filter for

This however requires a little used but very valuable syntax refinement. If you attempt the obvious expression “FavRat.Rating(*)” to mean “any defined rating for any brand”, as

    DefCon "FavRatT3BCVL", "Count of T3B Brands"
        AddInc "*", "FavRat.Rating(*)", "cvl_FavRatT3B", ""
    ConClose

then you’ll get a surprise:

The filter FavRat.Rating(*) is true for each of the three brand segments, so three values are output. To get just a single 0 or 1 instead, place the grid filter in square brackets (this is the syntax refinement):

    DefCon "FavRatT3BCVL", "Count of T3B Brands"
        AddInc "*", "[FavRat.Rating(*)]", "cvl_FavRatT3B", ""
    ConClose

Having square brackets around a grid filter means to return just a single true if any segment is true, or false if no segment is true.

By either method, you can now use FavRatT3BCVL in tables. As a sanity check, FavRatT3BCVL by FavRat should show empty cells for ratings 8/10 in column 0:

The complete turn-key script for the Ruby Demo job, using the grid filter approach (#2 above), is

#include $RubyUtilitiesLibrary.vbs
#include $RubyVariablesLibrary.vbs

Sub Main()

    Initialise()

    '' If you don't already have the flat FavRat_# variables in a grid
    '' (eg by import from SAV or SPS Blend or MDD/DDF)
    '' then generate and construct like this:

    DefGen "FavRat", "FavRat_$a($b)", "Favourability"
        AddLevel "a", "Brand",  "AidedBrandList", "*"
        AddLevel "b", "Rating", "FavRat_1",       "*"
    GenClose
    Construct "FavRat"

    '' T3B as flat
    DefGen "FavRatT3B", "FavRat.Brand($a)&FavRat.Rating(8/10)", "Favorability T3Box"
        AddLevel "a", "FavRatT3B", "FavRat.Brand", "*" 
    GenClose
    Construct "FavRatT3B"

    '' count the number of values in FavRatT3B if FavRat.Rating has a value,
    '' ie, write a 0 if no T3B for any brand
    DefCon "FavRatT3BCVL", "Count of T3B Brands"
        AddInc "*", "[FavRat.Rating(*)]", "cvl_FavRatT3B", ""
    ConClose
    Construct "FavRatT3BCVL"

    Cleanup()

End Sub

Assuming the rating grid is in hand, and that constructions are executed en mass from a ConstructAll() call elsewhere, this fulfills our initial mission in the six lines of script required to define FavRatT3B and FavRatT3BCVL.

Modify as appropriate for your own variable sets.