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

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

Agustin — Tue, 21 Apr 2020 11:34:15 GMT

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:

Desired split:

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

However using the split function:

Which gives me wrong results:

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

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

brady_brady — Tue, 21 Apr 2020 13:44:55 GMT

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

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

txnelson — Tue, 21 Apr 2020 14:31:51 GMT

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

I sorted the data table by ID and Name

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;

It created the following results
I then Split the table using ID, Name and Count in the Group selection box
Which gave me the results

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

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

brady_brady — Wed, 22 Apr 2020 19:31:58 GMT

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

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

Agustin — Wed, 22 Apr 2020 14:58:44 GMT

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?

Thanks

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

Agustin — Wed, 22 Apr 2020 15:20:26 GMT

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

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

brady_brady — Wed, 22 Apr 2020 19:33:23 GMT

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

Cheers,

Brady

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

Ressel — Sun, 14 Mar 2021 14:58:01 GMT

Sorry, just trying to figure out the purpose of "1" in your formula (col cumulative sum ( 1, :ID, :Method, :Name)). Is that the increment to be added for each row where the same combination of ID, Method & Name appears?

I checked the scripting index, but couldn't find info telling me what "1" does. Tested the formula a little and concluded that it's an increment. Is that correct? If yes, where in the scripting index or where at all could I find this information other than in this thread? (Just teaching myself to script, so please excuse foolish question.)

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

brady_brady — Sun, 14 Mar 2021 15:02:52 GMT

Hi,

The "1" is in the spot where you would normally place a column. The effect is a column of ones. I don't know if this technique is documented, per se. It is a nice trick to know, however, and I use it all the time in conjunction with cumulative sums to do what I've done here.

Cheers,

Brady