BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
DavidMoloney
Community Trekker

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.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions

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.

 

Learn it once, use it forever!
0 Kudos
12 REPLIES 12
cwillden
Super User

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.

-- Cameron Willden
0 Kudos
DavidMoloney
Community Trekker

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.

0 Kudos
txnelson
Super User

Re: Col Sum is referencing the wrong data table

try this form and see if it works

n_out = Col Sum( dt1:STATUS != "1");
Jim
0 Kudos
DavidMoloney
Community Trekker

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.

0 Kudos

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.

Learn it once, use it forever!
0 Kudos
DavidMoloney
Community Trekker

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?

 

0 Kudos

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?

Learn it once, use it forever!
0 Kudos
DavidMoloney
Community Trekker

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.

0 Kudos

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 );
Learn it once, use it forever!