59415_en-US

liliconcom

Col Sum is referencing the wrong data table

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.

Re: Col Sum is referencing the wrong data table

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.

 

Re: Col Sum is referencing the wrong data table

Why not?

Re: Col Sum is referencing the wrong data table

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?

Re: Col Sum is referencing the wrong data table

Same issue... Does my 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);

 

produce num_tall = 9 on your system?

 

I'm using JMP 13 if that's anything to do with it.

Re: Col Sum is referencing the wrong data table

My Dumb Question for the Day: what happens if you exit JMP and re-start it?

Re: Col Sum is referencing the wrong data table

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?

 

Re: Col Sum is referencing the wrong data table

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 );

Re: Col Sum is referencing the wrong data table

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.

Re: Col Sum is referencing the wrong data table

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.

Re: Col Sum is referencing the wrong data table

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.

Re: Col Sum is referencing the wrong data table

try this form and see if it works

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

Re: Col Sum is referencing the wrong data table

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.