415521_en-US

liliconcom

Split One Column and Transpose it as Header

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. 

</CODE></PRE><P><SPAN class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 999px;"><IMG src="https://us.v-cdn.net/6038925/uploads/images/35536i8BFFBFE73DC658B5/Capture.PNG" role="button" title="Capture.PNG" alt="Capture.PNG" /></SPAN></P><P>&nbsp;&nbsp;</P></LINGO-BODY><LINGO-LABS id="lingo-labs-415521" slang="en-US"><LINGO-LABEL>Scripting</LINGO-LABEL></LINGO-LABS><LINGO-SUB id="lingo-sub-415536" slang="en-US">Re: Split One Column and Transpose it as Header</LINGO-SUB><LINGO-BODY id="lingo-body-415536" slang="en-US"><P>What you want can be accomplished using the</P>
<P>&nbsp; &nbsp; &nbsp;Tables=>Split</P>
<P><SPAN class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="txnelson_0-1630733183189.png" style="width: 400px;"><IMG src="https://us.v-cdn.net/6038925/uploads/images/35537i8E626CF1FE4F430D/txnelson_0-1630733183189.png" role="button" title="txnelson_0-1630733183189.png" alt="txnelson_0-1630733183189.png" /></SPAN></P>
<P>&nbsp;</P>
<P>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</P>
<PRE><CODE class=" language-jsl">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;
);