Split Data Table Issue

[#1]

I am having some difficulty splitting tables and keeping the data grouped together correctly. Here is sample data:

V1	I1	Test Count	Sweep Name
0	0	1	Forward
0.1	0.1	1	Forward
0.2	0.2	1	Forward
0.3	0.3	1	Forward
0.4	0.4	1	Forward
0.5	0.5	1	Forward
0.6	0.6	1	Forward
0.7	0.7	1	Forward
0.8	0.8	1	Forward
0.9	0.9	1	Forward
1	1	1	Forward
1.1	1.1	1	Forward
1.2	1.2	1	Forward
0	0	2	Forward
0.1	0.1	2	Forward
0.2	0.2	2	Forward
0.3	0.3	2	Forward
0.4	0.4	2	Forward
0.5	0.5	2	Forward
0.6	0.6	2	Forward
0.7	0.7	2	Forward
0.8	0.8	2	Forward
0.9	0.9	2	Forward
1	1	2	Forward
1.1	1.1	2	Forward
1.2	1.2	2	Forward
0	0	1	Reverse
0.1	-0.1	1	Reverse
0.2	-0.2	1	Reverse
0.3	-0.3	1	Reverse
0.4	-0.4	1	Reverse
0.5	-0.5	1	Reverse
0	0	2	Reverse
0.1	-0.1	2	Reverse
0.2	-0.2	2	Reverse
0.3	-0.3	2	Reverse
0.4	-0.4	2	Reverse
0.5	-0.5	2	Reverse
0.6	-0.6	2	Reverse
0.7	-0.7	2	Reverse
0.8	-0.8	2	Reverse

When I split the table...

dt << Split(
	Split By( :Sweep Name ),
	Split( :V1, :I1 ),
	Output Table( "Split Table" ),
	Sort by Column Property

I get the following table:

Test Count	V1 Forward	V1 Reverse	I1 Forward	I1 Reverse
1	0	0	0	0
1	0.1	0.1	0.1	-0.1
1	0.2	0.2	0.2	-0.2
1	0.3	0.3	0.3	-0.3
1	0.4	0.4	0.4	-0.4
1	0.5	0.5	0.5	-0.5
1	0.6	0	0.6	0
1	0.7	0.1	0.7	-0.1
1	0.8	0.2	0.8	-0.2
1	0.9	0.3	0.9	-0.3
1	1	0.4	1	-0.4
1	1.1	0.5	1.1	-0.5
1	1.2	0.6	1.2	-0.6
2	0	0.7	0	-0.7
2	0.1	0.8	0.1	-0.8
2	0.2		0.2
2	0.3		0.3
2	0.4		0.4
2	0.5		0.5
2	0.6		0.6
2	0.7		0.7
2	0.8		0.8
2	0.9		0.9
2	1		1
2	1.1		1.1
2	1.2		1.2

JMP ends up aligning test count incorrectly for all the reverse data because the number of data points is not necessarily equal between any test.

What I want is the following:

Test Count	V1 Forward	V1 Reverse	I1 Forward	I1 Reverse
1	0	0	0	0
1	0.1	0.1	0.1	-0.1
1	0.2	0.2	0.2	-0.2
1	0.3	0.3	0.3	-0.3
1	0.4	0.4	0.4	-0.4
1	0.5	0.5	0.5	-0.5
1	0.6		0.6
1	0.7		0.7
1	0.8		0.8
1	0.9		0.9
1	1		1
1	1.1		1.1
1	1.2		1.2
2	0	0	0	0
2	0.1	0.1	0.1	-0.1
2	0.2	0.2	0.2	-0.2
2	0.3	0.3	0.3	-0.3
2	0.4	0.4	0.4	-0.4
2	0.5	0.5	0.5	-0.5
2	0.6	0.6	0.6	-0.6
2	0.7	0.7	0.7	-0.7
2	0.8	0.8	0.8	-0.8
2	0.9		0.9
2	1		1
2	1.1		1.1
2	1.2		1.2

I have played around with the group option with split data table but it always seems to drop off data. For example, if I group by test count, it only gives me a single row per test count and seems to pick whatever the last value is in that test count for forward and reverse.

Hopefully this all makes sense. Thanks in advance for the assistance.

[#2 · Accepted solution]

Hi

My solution include two steps. Transpose then Split.

Before table change, I create a new col named "Tag" with formula

Current Data Table() << New Column( "Tag",
formula( Col Rank( :Test Count, :Test Count, :Sweep Name ) )
);

Transpose data table

Current Data Table() << Transpose(
	columns( :V1, :I1 ),
	By( :Test Count, :Tag ),
	Label( :Sweep Name ),
	Output Table( "Transpose of Untitled" )
);

Then split as last step

Data Table( "Transpose of Untitled" ) << Split(
	Split By( :Label ),
	Split( :Forward, :Reverse ),
	Sort by Column Property
);

Final table like this

Have a good day!

[#3]

View more...

That's very clever and works perfectly. Thank you!