cancel
Showing results for
Show  only  | Search instead for
Did you mean:
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
Level V

## How to group using last 2 characters and subset the latest data?

Hi all,

Is it possible to make a column for groupings using the last two characters from another column? For example, I have this table:

 FYW Groupings FY24W38 1 FY24W38 1 FY24W39 2 FY24W39 2 FY24W39 2 FY24W40 3 FY24W41 4 FY24W41 4

Is it also possible to subset the first 2 latest date according to the grouping? In this example, FY24W40 and FY24W41 will be selected then subset since they are the "latest" data. TIA

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to group using last 2 characters and subset the latest data?

Here is the formula to use to create the groupings

``Col Cumulative Sum( Right( :FYW, 2 ) != Lag( Right( :FYW, 2 ) ) )``

Here is a simple script to find the 2 highest values of FYW and to create a subset with only those rows that match the highest 2 values

``````names default to here(1);

dt=current data table();

array=associative array(:fyw<<get values)<<get keys;

dt << select where(:FYW == array[length(array)-1] | :FYW == array[length(array)] );

dtSubset = dt << subset( selected rows( 1 ), selected columns( 0 ) );``````
Jim
2 REPLIES 2
Super User

## Re: How to group using last 2 characters and subset the latest data?

Here is the formula to use to create the groupings

``Col Cumulative Sum( Right( :FYW, 2 ) != Lag( Right( :FYW, 2 ) ) )``

Here is a simple script to find the 2 highest values of FYW and to create a subset with only those rows that match the highest 2 values

``````names default to here(1);

dt=current data table();

array=associative array(:fyw<<get values)<<get keys;

dt << select where(:FYW == array[length(array)-1] | :FYW == array[length(array)] );

dtSubset = dt << subset( selected rows( 1 ), selected columns( 0 ) );``````
Jim
Super User

## Re: How to group using last 2 characters and subset the latest data?

It can also be a good idea to consider creating new column for your weeks (might make it simpler for you to solve) and then use that and you can easily create this column using Recode

Then create your grouping column with a formula (using same formula as Jim but "newly" created column W)

``````	Col Cumulative Sum(:W != Lag(:W))
``````

and then you can create summary (this can be built in many different ways depending on how you want to select your values), change Group order, select first two rows from summary and create your subset from your original table

``````dt_summary << Select Rows([1, 2]);
dt_subset = dt << Subset(Selected Rows(1), columns(:FYW));
Close(dt_summary, no save);``````
-Jarmo