Build FORMULA recognizing change of individes in a data long format dataset

[#1]

Hi,

I have my dataset in so called long format, i.e. many individuals with repeated measures and on row for each time point. All variables are in columns.

How can I build a FORMULA to recognize each individual and change of individual. So I need the answer for each row for each time point.

So for instance, to group data, I would like the formula to assign by the last time point (varies between individuals =Dog) if it is a "N" to write "N" for all rows for that Dog and Y if there is one "Y" in the string. In the example above row 5 is the last for Dog "AE1" and the formula should write "N" for all rows 1-5.

For Dog "AE2" it should write "Y" for rows 6-8 and so on.

The same principle should apply for other formulas e.g. to calculate time differnces between rows with first/last  visit for each dog as refernce cell.

Hope someone has solved this problem.

The other way is easy, if you have formulas by column you just add arguments and the formula returns the answer in a new COLUMN.

[#2 · Accepted solution]

The task appears to be difficult to solve with a formula, i.e. a column formula. It may be possible but a script would be more effective if the table contains many rows.

Anyway, for the example on populating a column with Y/N the formula below may work. It will set "Y" to all rows of a dog if column "HF" cantain at least one Y, i.e. not only if the last row is Y.

Run this script to see the result of the formula

// An example table based on the screenshot

New Table( "Dogs",

          Add Rows( 8 ),

  New Column( "Dog",

                    Character,

                    Nominal,

                    Set Values( {"AE1", "AE1", "AE1", "AE1", "AE1", "AE2", "AE2", "AE2"} )

          ),

          New Column( "HF", Character, Nominal, Set Values( {"N", "N", "N", "N", "N", "N", "N", "Y"} ) ),

  New Column( "HF end",

                    Character,

                    Nominal

          )

);

// Set formula

Column("HF end")<<set Formula( If( Col Maximum( Hex To Number( Char To Hex( :HF ) ), :Dog ) == 89, "Y", "N" ) );

[#3]

Thanks. It did the work.

With this kind of script it is fast to make new groups. But for future needs it needs to be modified. What does the number 89 stand for?

[#4]

89 happens to be the the numerical counterpart to "Y". I dont know Y.