Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Col Sum is referencing the wrong data table

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 6, 2018 5:02 AM
(1577 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!