turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Build FORMULA recognizing change of individes in a data long format dataset

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 15, 2012 11:17 AM
(2711 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 15, 2012 1:49 PM
(4749 views)

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 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 15, 2012 1:49 PM
(4750 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 16, 2012 12:52 AM
(2655 views)

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 16, 2012 6:01 AM
(2655 views)

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