Scripting with multiple tables

[#1]

I'm relatively new to JSL (and scripting in general) and am having trouble figuring out a way of manipulating multiple tables.

What I would like to do for each row of one table is turn the data from two columns into two global variables, adjust matching data in a second table, then go on to the next row of the first table, repeat.

IE:

One table has a total number of runs for each workweek, one row per week. My second table has many rows for each workweek, but I'd like each row to get the appropriate total runs for that week placed in the appropriate column.

In the scripting guide, calling "Current Data Table(desired table)" is listed as how to move your references from table to table, but I can't seem to make it work.

I'm trying to make something like the following work (the shows are just in there for troubleshooting):

Current Data Table("Percent Fail") ;

For Each Row(

          globalWeek= :Week;

          globalTotal = :N Rows;

          Show(globalWeek);

          Show(globalTotal);

  Current Data Table("Run Summary Table");  

For Each Row( if(Week == globalWeek,

Runs that Week=globalTotal))

)

Is what I'm trying to do possible? Even if I end up finding a different way of solving this particular situation, I'm curious about the general process of going between two tables like this.

[#2]

It's easier if you point different variables to the two tables.

pf_dt = data table("Percent Fail");

rst_dt = data table("Run Summary Table");

To use these variables:

for (i = 1, i <= nrows(pf_dt), i++,

    one_week = column(pf_dt, "Week");

    one_total = column(pf_dt, "N Rows");

    for (k = 1, k <= nrows(rst_dt), k++,

        if (column(rst_dt, "Week") == one_week,

            column(rst_dt, "Runs that Week") = one_total;

        );

    );

);

However you'd be better off using a JOIN command to join the two tables.  Use the Tables > Join command to create a new table, and then click on the red triangle to see the JSL commands that were used.

[#3]

Thanks very much for the quick reply.

The reason I'm avoiding Join is that I'd like to be able to run this repeatedly on the same table, as more data is added over time.

After thinking about it, though, I suspect I'm making it all far more complicated than necessary.

What I'd really like is to, in my original table, count how many rows are present for each week, and then put that total rows per week count into a column.

So that basically it would look like this:

Week,     RowsPerWeek

5,               3

5,               3

5,               3

6,               1

7,               2

7,               2

This would be a script I'd run every time I added data to the sheet, and then automatically it would sum my per-week rows and update that column. I was getting there through a summarize-table and then putting the data back into the original table, but there has to be an easier way.

[#4]

n such a case you don't need a script. A column formula would do.

Try this formula in RowsPerWeek:

Col Number( :Week, :Week )

[#5]

Oh that's wonderful, I hadn't figured out the column statistics formulas yet - I've only done straight within-the-same-row formulas.

Playing with that a bit, it works beautifully for numeric columns, is there a way of doing the same thing with character columns?

(And yes, I've already bought a book, but it'll take a little while to get to me from amazon.)

[#6 · Accepted solution]

Col Number does not like character formatted columns but try this trick:

Col Number( Hex To Number( Char To Hex( :Column ) ), :Column )

[#7]

It works, thanks again.

Out of curiosity - when I look up Col Number it claims it takes only one argument and that it gives the number of "non missing" rows. Obviously giving it two forces some sort of comparison, but what exactly is it doing?

[#8]

The second argument is optional and represent a "by variable", i.e. it returns separate counts for every level of the by-column.

Under the help menu/JSL functions it is documented (these syntax indexes are very helpful, sometimes more comprehensive than the books or help pages and provide good examples).