cancel
Showing results for
Show  only  | Search instead for
Did you mean:
Choose Language Hide Translation Bar
Level IV

## First Value for a Unique ID

I am trying to select the first value for each unique ID and save it to a new column. For instance in the example below, the 2k71 would be 51 and 3SJ42 would be 49. How would I write a formula to find only the first value for each unique ID?

1 ACCEPTED SOLUTION

Accepted Solutions
Staff

## Re: First Value for a Unique ID

Hello,

``Col Minimum( Col Cumulative Sum( :Value, :ID ), :ID )``

Cheers,

5 REPLIES 5
Staff

## Re: First Value for a Unique ID

EDIT:

////////////////

Hi,

The formula below will do this. It returns the minimum row number in the column, grouped by ID.

Cheers,

``Col Minimum( Row(), :ID )``

Super User

## Re: First Value for a Unique ID

If you want the value to be also in rows which have missing value this could work:

``````ids = Loc(:ID << get as matrix, :ID);
first_val = Min(Loc Nonmissing(:Value[ids]));
:Value[ids[first_val]];``````

I strongly suggest that you take a look at Loc() function from Scripting Index and possibly run the formula in smaller parts so you understand how it works. There are most likely easier ways to do this, but I like using Loc().

-Jarmo
Super User (Alumni)

## Re: First Value for a Unique ID

Here is one way that:

• first finds the first finds the smallest row with and without missing values (red box)
• excludes those missing values returning the row for each ID type (purple box) and then
• gets the value for that row.

Here is a script you can run to recreate this column with a few steps along the way:

View more...
``````New Table( "First Value for a Unique ID",
New Column( "ID",
Character,
"Nominal",
Set Values(
{"2k", "2k", "2k", "2k", "2k", "2k", "l5", "l5", "l5", "l5", "l5", "l5",
"l5", "l5", "3s", "3s", "3s", "3s", "3s", "3s", "3s", "3s"}
)
),
New Column( "Value",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values(
[., ., 51, 50, 52, 54, ., ., ., 53, 54, 55, 57, 58, ., 49, 48, 50, 52,
56, 58, 60]
)
),
New Column( "Row with First Value with/without missing 2",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Col Minimum( Row(), :ID, !Is Missing( :Value ) ) )
),
New Column( "Row with First Value Exclude Missing",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Is Missing( :Value ),
Empty(),
Col Minimum( Row(), :ID, !Is Missing( :Value ) )
)
)
),
New Column( "Row with First Value",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
Col Mean(
If( Is Missing( :Value ),
Empty(),
Col Minimum( Row(), :ID, !Is Missing( :Value ) )
),
:ID
)
)
),
New Column( "First Value",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
:Value[Col Mean(
If( Is Missing( :Value ),
Empty(),
Col Minimum( Row(), :ID, !Is Missing( :Value ) )
),
:ID
)]
)
)
)``````

Staff

## Re: First Value for a Unique ID

Hello,

``Col Minimum( Col Cumulative Sum( :Value, :ID ), :ID )``

Cheers,