Split One Column and Transpose it as Header

[#1]

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. 

[#2 · Accepted solution]

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;
);

[#3]

Thanks Jim

Thanks TS

[#4 · Accepted solution]

Hi,

I am not sure I understand your problem: if you do a simple Split of column Quantity by "Order Number" and group by "Item" and "Part Number" you get the outcome as shown in your post with the exception that the missing values are coded as "missing" and not "0".

With a simple Recode from the Standardize Attributes, you can set all missing values to zero.

Assuming that I did not understand your initial request, could you clarify what part of the process did not work for you?

Best,

TS