topic Split One Column and Transpose it as Header in Discussions

Split One Column and Transpose it as Header

Lino — Fri, 09 Jun 2023 19:55:59 GMT

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.

Re: Split One Column and Transpose it as Header

txnelson — Sat, 04 Sep 2021 05:28:50 GMT

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

Re: Split One Column and Transpose it as Header

Thierry_S — Sat, 04 Sep 2021 05:32:35 GMT

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,

Re: Split One Column and Transpose it as Header

Lino — Sat, 04 Sep 2021 05:59:47 GMT

Thanks Jim

Thanks TS