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
- :
- Searching a value in a column- JSL

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
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 12, 2017 10:43 AM
(2017 views)

Hi All,

Can anyone help me with the search for a particular value in a column and try to find the count of that value with the overall count? In the below example, I am trying to search for value "1" in B column. I found 3 occurences in 10 instances so C is marked with 30%. Similarly for A=2, I found 4 occurence of "1" in 12 instances, so C is marked with 33%.

A | B | C |

1 | 1 | 30% |

1 | 87 | |

1 | 90 | |

1 | 65 | |

1 | 1 | |

1 | 65 | |

1 | 3 | |

1 | 1 | |

1 | 34 | |

1 | 5 | |

2 | 1 | 33% |

2 | 5 | |

2 | 2 | |

2 | 47 | |

2 | 1 | |

2 | 54 | |

2 | 1 | |

2 | 85 | |

2 | 1 | |

2 | 1 | |

2 | 22 | |

2 | 21 |

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions

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

Apr 12, 2017 1:50 PM
(3958 views)

Solution

I think this should work:

col number(:b, :a, :b )/col number(:b, :a)

You need to take a look at the documentation on Col Number() in the Scripting Index

Help==>Scripting Index==>Col Number

Jim

10 REPLIES

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

Apr 12, 2017 10:57 AM
(2015 views)

You can use the Tabulate Platform and specify the % Column Total as the statistic.

You can also create a new column and use the following formula to get the answer

`Col Number( :b, :b ) / Col Number( :b )`

Jim

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

Apr 12, 2017 12:29 PM
(2004 views)

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

Apr 12, 2017 1:04 PM
(1995 views)

:b represents column "b", from the data table you listed in the original question. The formula,

Col Number( :b, :b) / Col Num( :b )

is interpreted as:

the count of all of the values in column b, for the current rows value of column b, divided by the total count of the values for column b.

In the example data you provided, the calculation for the first row would be:

The count of values found in column b, that match the value for column b. That is, the value of the first row for column b is "1", so it would count the number of "1"s found in column b, which is 8. It would then divide by the total number of the values found in column b, which is 22.

Concerning the Tabulate Platform, what you would do, is to change the modeling type of column b to Nominal, so Tabulate will treat the column as a grouping column. Then you would drag column b to the Drop Zone for Rows. I will then list out each of the values found for column b. By default, the statistic column that is listed is the count(N). To change it to the Column %, you would just drag the Column % statistic to the "N" column, and it would replace the statistic with the column %

Jim

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

Apr 12, 2017 1:35 PM
(1991 views)

Thanks.

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

Apr 12, 2017 1:50 PM
(3959 views)

I think this should work:

col number(:b, :a, :b )/col number(:b, :a)

You need to take a look at the documentation on Col Number() in the Scripting Index

Help==>Scripting Index==>Col Number

Jim

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

Apr 12, 2017 5:49 PM
(1972 views)

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

Apr 13, 2017 1:17 AM
(1962 views)

HI

I checked the Col Number. Is this what you mean? but it won't work.

Names Default To Here( 1 );

Open( "$SAMPLE_DATA/Big Class.jmp" );

Col Number( :height,:height )/Col Num( :height )

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

Apr 13, 2017 5:35 AM
(1951 views)

you misspelled "col number()" as "col num()"

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column( "thepercent",
formula( Col Number( :height, :height ) / Col Number( :height ) )
);
```

Jim

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

Apr 13, 2017 9:29 AM
(1944 views)

This widget could not be displayed.