topic How to group using last 2 characters and subset the latest data? in Discussions

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

UserID16644 — Mon, 22 Apr 2024 23:39:39 GMT

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

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

txnelson — Tue, 23 Apr 2024 01:56:35 GMT

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 ) );

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

jthi — Tue, 23 Apr 2024 04:26:50 GMT

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);