Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Agustin
Level III

How to split table and keep all columns with correct information?

I'm trying to split a table by "Method", so each method becomes a new column, with the values associated. But I want to keep all the other columns, ID and Name.

Initial table:

Agustin_0-1587468541593.png

Desired split:

Agustin_1-1587468598511.png or 

Agustin_2-1587468628834.png

Either of the formats pictured above would work for my purposes.

 

However using the split function:

Agustin_3-1587468670930.png

Which gives me wrong results:

Agustin_4-1587468686842.png

For example, ID = 3, B = 222 was done by Jon, not Alice. So the values for ID and Name don't always match the value in the method columns.

 

Is there a built-in way of achieving this? It appears to be a fault with the split function.

 

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: How to split table and keep all columns with correct information?

EDIT: I mis-typed one of the column names, "Method", as "group" in the original version of this reply. This edit remedies that error.

 

Apologies,

Brady

 

 

*****************************************

 

Oh, good catch, I did not see that duplication. In that case, yes, you need another column.

 

The way I create that new "differentiator" column that txnelson mentions--without lags, so you don't have to sort the table--is with the formula:

 

col cumulative sum ( 1, :ID, :Method, :Name)

 

Cheers,

Brady

View solution in original post

6 REPLIES 6
Highlighted

Re: How to split table and keep all columns with correct information?

Hi Augustin,

 

In the dialog launch menu, cast the "ID" and "Name" columns into the "Group" role.

 

More information can be found on the split function here: https://www.jmp.com/support/help/en/15.1/index.shtml#page/jmp/examples-of-splitting-columns.shtml

 

Cheers,

Brady

Highlighted
txnelson
Super User

Re: How to split table and keep all columns with correct information?

If you did not have duplicate values for ID and Name, you could place them into the Group selection box in the Split platform, and get the results you want, but because you have two entries for Bob in ID group 1, you have to create a new column that will differentiate for that.  So how I did that was

  1. I sorted the data table by ID and Name
  2.  I created a new formula column called Count and used the formula
    If( Row() == 1, counter = 0 );
    If( :ID == Lag( :ID ) & :Method == Lag( :Method ) & :Name == Lag( :Name ),
    	counter++,
    	counter = 1
    );
    counter;
  3. It created the following results split1.PNG
  4.  I then Split the table using ID, Name and Count in the Group selection boxsplit2.PNG
  5.  Which gave me the resultssplit3.PNG

Please note, that I keyed in an incorrect value in the original data table for Bob of 1223 and it should have been 123

Jim
Highlighted

Re: How to split table and keep all columns with correct information?

EDIT: I mis-typed one of the column names, "Method", as "group" in the original version of this reply. This edit remedies that error.

 

Apologies,

Brady

 

 

*****************************************

 

Oh, good catch, I did not see that duplication. In that case, yes, you need another column.

 

The way I create that new "differentiator" column that txnelson mentions--without lags, so you don't have to sort the table--is with the formula:

 

col cumulative sum ( 1, :ID, :Method, :Name)

 

Cheers,

Brady

View solution in original post

Highlighted
Agustin
Level III

Re: How to split table and keep all columns with correct information?

Thank you. I assume in your answer the ":group" should not be there?

Also when I try it without :group I don't get the same numbers as for txnelson's answer.

Could you point me to what is going wrong?

Agustin_0-1587567438917.png

 

Thanks

Highlighted
Agustin
Level III

Re: How to split table and keep all columns with correct information?

Actually where you said group you meant method. It works perfect thank you!
Highlighted

Re: How to split table and keep all columns with correct information?

Thanks for the heads-up on my error--I've edited the offending post, so it now has the correct formula.

 

Cheers,

Brady

 

Article Labels

    There are no labels assigned to this post.