cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
jojmp
Level III

How to merge rows and sum them?

How can we merge rows of a table as shown below:

XY     
STATE_1 1 STATE_1 6896 
STATE_1 2 STATE_2 7 
STATE_1 3 STATE_3 22 
STATE_2 4 STATE_4 861 
STATE_2 3 STATE_2 9494 
STATE_3 2 STATE_1 37412 
STATE_3 5 STATE_2 4 
STATE_3 10 STATE_3 18 
STATE_3 5 STATE_4 353 
STATE_4 576 STATE_2 9494 
STATE_4 33 STATE_1 3737 
STATE_4 25 STATE_2 44 
STATE_4 97     
STATE_4 54     
STATE_4 76     
STATE_2 94     
STATE_1 37     
STATE_2 4     

 

1. First group the continuous occurrence of rows

2. Calculate the sum of sequential occurrence of STATE_1 to STATE_4

 

Pfa the sample table for your reference

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How to merge rows and sum them?

I solved the problem using 2 new formula columns

doublesum.PNG 

The Group column formula just creates a grouping number when :Big Value is detected

If( Row() == 1, theGroup = 0 );
If( Is Missing( :Big Value ) == 0,
	theGroup
	++);
theGroup;

The Sum column formula just looks one row ahead, and if it detects a non missing value for :Big Value, it adds up the sums for both :Value and :Big Value for the current Group and then adds them together

If( Is Missing( :Big Value[Row() + 1] ) == 0,
Sum( Col Sum( :Value, :Group ), Col Sum( :Big Value, :Group ) ),
If( Row() == N Rows( Current Data Table() ) & Is Missing( :Big Value ) == 0,
Sum( :Value, :Big Value )
)
)

Attached is the data table I used

Jim

View solution in original post

1 REPLY 1
txnelson
Super User

Re: How to merge rows and sum them?

I solved the problem using 2 new formula columns

doublesum.PNG 

The Group column formula just creates a grouping number when :Big Value is detected

If( Row() == 1, theGroup = 0 );
If( Is Missing( :Big Value ) == 0,
	theGroup
	++);
theGroup;

The Sum column formula just looks one row ahead, and if it detects a non missing value for :Big Value, it adds up the sums for both :Value and :Big Value for the current Group and then adds them together

If( Is Missing( :Big Value[Row() + 1] ) == 0,
Sum( Col Sum( :Value, :Group ), Col Sum( :Big Value, :Group ) ),
If( Row() == N Rows( Current Data Table() ) & Is Missing( :Big Value ) == 0,
Sum( :Value, :Big Value )
)
)

Attached is the data table I used

Jim