cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
sgschauer
sgschauer
Level I

How to drop duplicates and only retain maximum value

Sep 28, 2023 08:41 PM (858 views)

I'm trying to consolidate this data table.  I previously used Excel for it but it is >18,000,000 lines of data which is too big for Excel.  I have multiple different values for the same subject and I only want to retain the maximum value and delete the rest.  The table-join-table method does not have a maximum value only option.  How do I go about consolidating this?

Preview file
143 KB
0 Kudos
1 REPLY 1
jthi
Super User jthi
Super User

Re: How to drop duplicates and only retain maximum value

Sep 29, 2023 12:37 AM (854 views)  |  Posted in reply to message from sgschauer 09-28-2023

Below are few options

  1. Create summary table where you use columns you wish to keep as group columns and then calculate max if the value you want the max from
  2. Sort the data based on the value you want to max value for and use Select Duplicate Rows when you have grouping columns selected and delete the selected rows
  3. Write a formula formula which will add new column to tell if column is max or not
    1. :VALUE == Col Max(:VALUE, :GROUP1, :GROUP2) or something similar
-Jarmo