Split One Column and Transpose it as Header

Lino

Hi, 

I am having problem to split columns and transpose it as header and value. Need help here how to write the code.

My data table has four columns, Item, Part Number, Order Number and Quantity.

I need to split Order Number and transpose it as header in new column and split Quantity column transpose it to respective cell.

I attach my data table here and required outcome for your reference. 

 

I know this can be done by using Tabulate (). But I do not want to use it because I need my outcome data table is able to link to the original data table for further processing. 

  

Data Table.jmp

txnelson

What you want can be accomplished using the

     Tables=>Split

 

Below is a simple script taking the JSL provided from the Split Platform, and then just adding on the JSL taken from the Recode feature

Names Default To Here( 1 );
dt = Current Data Table();

// Get the column names that will need to recoded to 0 if missing
Summarize( dt, colnames = by( :Order Number ) );

// Do the simple split to transform the data table
dtSplit = dt << Split(
	Split By( :Order Number ),
	Split( :Quantity ),
	Group( :Item, :Part Number ),
	Sort by Column Property
);

// Loop across all split columns and recode missing values to zeros
For( i = 1, i <= N Items( colnames ), i++,
	dtSplit << Begin Data Update;
	dtSplit << Recode Column(
		As Column( dtSplit, colnames[i] ),
		{Map Value( _rcOrig, {., 0}, Unmatched( _rcNow ) )},
		Update Properties( 1 ),
		Target Column( As Column( dtSplit, colnames[i] ) )
	);
	dt << End Data Update;
);