nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Col Sum is referencing the wrong data table

DavidMoloney

I have two data tables open in my script, say dt1 and dt2, and they were opened in that order. dt1 has a column with a status variable, where anything other than "1" represents a type of outlier. I want to count the number of outliers in dt1. This is what I have...

n_out = Col Sum(If( dt1:STATUS == "1", 0, 1));

However this doesn't seem to be working. It seems that the code about is trying to reference dt2 as it was opened more recently. If I click on dt1 to bring the window to the front, and then run the code again, it works. Why is my reference of dt1:STATUS not working?

Thanks for your help.

Accepted answers

Mark_Bailey

This code also produces the correct value of 9:

Names Default to Here( 1 );

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );

dt1 << New Column( "Outlier", Formula( :height > 65 ) );
n outliers = Col Sum( dt1:Outlier );

Here I used another data column with a formula that uses the expression to test for an outlier. This result tells me that Col Sum() might not be intended to work with any expression but specifically with a data column. (Hint, hint: COL SUM().) I don't know where the value of 4 came from when using the expression for the outlier test instead of a data column reference.

All comments

cwillden

Based on your description, that sounds like it should definitely not be happening. I don't see any issues in your code. Might be a good time to call JMP tech support.

txnelson

try this form and see if it works

n_out = Col Sum( dt1:STATUS != "1");

DavidMoloney

I have also tried the following:

n_out = Col Sum(NOT( dt1:STATUS == "1" ))

and it still doesn't work.

I know that the answer for a particular example is 3. On my screen I see dt1 in the background as it was opened first, and then dt2 over that. When I run this line of code I get n_out = 0. But if I click on the table dt1 (bringing it to the forefront of my screen) and then run the code again, I get n_out = 3.

This is why I think the issue is with referencing the wrong table.

DavidMoloney

Thanks for responding.

I get the same issue.

I know that the answer for a particular example is n_out = 3. On my screen I see dt1 in the background as it was opened first, and then dt2 over that. When I run this line of code I get n_out = 0. But if I click on the table dt1 (bringing it to the forefront of my screen) and then run the code again, I get n_out = 3.

This is why I think the issue is with referencing the wrong table.

Mark_Bailey

Why don't you step back from the original problem and try something simpler. For example, with different values in each data table, try referencing just one location to see which value is returned. For example,

Names Default to Here( 1 );

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );

value = Column( dt1, 4 )[2]; // second row of :height in Big Class?
Show( value );

It seems to be working.

Mark_Bailey

You could also try this code instead:

Names Default to Here( 1 );

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );

n ht over 60 = Sum( (Column( dt1, 4 ) << Get As Matrix) > 60 );

DavidMoloney

Great idea... So I have run the following code:

Names Default to Here( 1 );

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );

num_tall = Col Sum( dt1:height > 65);
show(num_tall);

This is the out put:

Names Default to Here( 1 );

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );

num_tall = Col Sum( dt1:height > 65);
show(num_tall);
/*:

num_tall = 4;

Then when I click on table Big Class (bringing it to the forefront), I get:

num_tall = Col Sum( dt1:height > 65);
show(num_tall);
/*:

num_tall = 9;

Which is the correct answer.

Is there something wrong with my settings, or is this just the way JSL is designed?

DavidMoloney

Ok... Ok.... The following produced the count to 9.

Names Default to Here( 1 );

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );

n ht over 60 = Sum( (dt1:height << Get As Matrix) > 65 );

Does this mean that converting to matrix is a solution?

Mark_Bailey

Why not?

Mark_Bailey

This code also produces the correct value of 9:

Names Default to Here( 1 );

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );

dt1 << New Column( "Outlier", Formula( :height > 65 ) );
n outliers = Col Sum( dt1:Outlier );