nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to hide/exclude duplicate IDs except for the first ID?

hyunseolee120

Hello awesome people,

I am new to JMP and needs some help with cleaning the data set.

I have several duplicates in my data set, where one individual is admitted multiple times in a given period.

I want only the first episode of the individual, according to the admission date (m/d/yyyy)

For instance,

Respondent with an ID number ABC11 appeared twice in my data set. The respondent was admitted to the treatment in 7.14.2013 and then readmitted in 8.25.2014. I want to keep only the information of ABC11 in 7.14.2013)

Thanks to previous discussions, I figured how to remove the duplicates joining the table, matching the columns, and dropping duplicates. This way, I create a new data file with Unique client sample (n=4,000) without the duplicates from the original file with total admitted sample (n=6000)

However, now I want to hide/exclude those duplicates and leave them in the same data file,

so that I can flexibly compare and contrast Total Admitted sample (n=6000, with duplicates) and Unique client sample (n=4000, without duplicates) in later analysis.

Unless, I'll have to run analysis separately and combine the result in data reports, sounds like a hassle.

Blessings,

Accepted answers

Sorry, I was too quick to answer. The Date column should not be a grouping variable in this case.

Sort by date and try this:

dt << select where(Col Min(Row(), :ID) < Row()) << hide and exclude;

All comments

With JSL:

// Exclude all but first row of multiples (sort by date to get the earliest...)

dt = Current Data Table();

dt << select where(Col Min(Row(), :Respondent, :Date) < Row()) << hide and exclude;

Same approach with column formula:

dt<<new column("exclude", Row State, Formula(ExcludedState(Col Min(Row(), :Respondent, :Date) < Row())))

hyunseolee120

Hello thanks for your answer,

I copied and pasted the JSL into my script but for some reason it didn't work.

More precisely, it worked only for few but not all.

Didn't work for these ones.

As you can see (sorry for the bad resolution), we have total three ID AL2063098, and I wanted row number 333 and 334 to be deleted but only row 334 was excluded/hided.

Below is the JSL I used.

// Exclude all but first row of multiples (sort by date to get the earliest...)

dt = Current Data Table();

dt << select where(Col Min(Row(), :ID, :Date) < Row()) << hide and exclude;

Sorry, I was too quick to answer. The Date column should not be a grouping variable in this case.

Sort by date and try this:

dt << select where(Col Min(Row(), :ID) < Row()) << hide and exclude;

hyunseolee120

Thank you so much, it worked!

If you have time, do you care to elaborate on the meaning of this script?

Or where can I find a way to learn how to use JSL?