Column reference and sort/Stack

vishwasanj

Hi All,

 

I have been working on this dilemma for a while. 

 

I have a data table and I need to sort series of columns in ascending order which comes after a specific column (Col_Y), 

 

jdt=kdt << Sort(
By( :Name( "0 0" ), :Name( "168H 85C" ), :Name( "504H 85C" ) ), // These columns vary from 2 to 5 or more..
Order( Ascending, Ascending, Ascending )
);

// I do some color coding in the middle

After sorting, I need to stack them up,

edt=jdt<< Stack(
columns( :Name( "0 0" ), :Name( "168H 85C" ), :Name( "504H 85C" ) ),
Source Label Column( "Label_bh_bt" ),
Stacked Data Column( "Data_bh_bt" )
);

 

 

My problem is I cannot use this in the code since the numbers and column keeps varying. 

 

I understand how to get the reference of a column by 

list = kdt << get column reference();
position = Contains( list, Column( "Col_Y" ) );

 

How do I move my reference pointer  to the next column and probably sort/stack them till I hit empty{} ?

 

I really appreciate your help.

Thank you.

 

 

 

txnelson

See if this helps you out

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Subset of semiconductor capability",
	Add Rows( 10 ),
	New Column( "Col_Y1",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 104.412948990151 ), USL( 131.893493064355 ), Target( 118.153221027253 )}
		),
		Set Values(
			[114.555760105678, 120.043693059459, 114.926510466106, 111.756445551183, 111.545085144313,
			113.52359591958, 111.749308571209, 114.411433304976, 118.48975048042, 113.171122695149]
		)
	),
	New Column( "PNP1",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 164.389518443879 ), USL( 429.64634592871 ), Target( 297.017932186294 )}
		),
		Set Values(
			[322.616751775796, 333.128079728678, 348.978757216574, 268.548094462492, 295.073165929223,
			323.833298568833, 369.320490694357, 342.987400315929, 315.52243608245, 288.978183718528]
		)
	),
	New Column( "PNP2",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( -136.122122529984 ), USL( 1067.00612015088 ), Target( 465.441998810449 )}
		),
		Set Values(
			[469.390289765043, 437.781112798903, 532.12814730405, 373.058628529111, 338.900737368942,
			469.992171436316, 563.084459366587, 479.378108668814, 530.265608347575, 421.454501563007]
		)
	),
	New Column( "Col_Y2",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 96.5938056459265 ), USL( 130.90419774122 ), Target( 113.749001693573 )}
		),
		Set Values(
			[115.958473043434, 120.974137316372, 117.75312418797, 114.092607737274, 113.778098130642,
			116.488049497674, 115.935283026742, 115.762801088796, 116.347793739608, 116.232907922575]
		)
	),
	New Column( "PNP3",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 118.677820430348 ), USL( 141.901766142544 ), Target( 130.289793286446 )}
		),
		Set Values(
			[130.378809500886, 132.736937590235, 136.831952164704, 136.969154239163, 136.622623419893,
			137.480356512481, 138.094011176166, 142.10586919757, 134.750041237898, 129.719685093372]
		)
	),
	New Column( "IVP1",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 59.6200689219354 ), USL( 67.2001519300058 ), Target( 63.4101104259706 )}
		),
		Set Values(
			[73.4842940932646, 75.6074915248983, 73.3304733114595, 75.7647427124531, 70.5460961797334,
			72.9451281758323, 75.6756511920958, 76.4874855218512, 66.4572544989587, 72.2503891181424]
		)
	),
	New Column( "PNP4",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( -54.4319216800649 ), USL( 531.90910950445 ), Target( 238.738593912193 )}
		),
		Set Values(
			[262.351408960942, 269.950148249765, 273.273874690651, 236.935577128975, 244.380589490586,
			267.186035095484, 290.774763825391, 282.589770870314, 258.238866835766, 241.25222968008]
		)
	),
	New Column( "NPN3",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 97.317681821994 ), USL( 144.291664077077 ), Target( 120.804672949535 )}
		),
		Set Values(
			[119.47852252254, 122.25499204522, 120.033132204358, 116.970066814936, 116.214953853587,
			120.041044282935, 115.572158051301, 118.992345590077, 120.928324071462, 117.320359424529]
		)
	),
	New Column( "Col_Y3",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 139.200434284046 ), USL( 145.409947550315 ), Target( 142.305190917181 )}
		),
		Set Values(
			[139.588801950955, 144.633487927042, 136.369288337148, 146.477383476889, 132.328519479399,
			138.566660436878, 151.03999258707, 145.856209565548, 131.365614418469, 134.152985449894]
		)
	),
	New Column( "NPN4",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 95.8856688269302 ), USL( 115.889615922419 ), Target( 105.887642374674 )}
		),
		Set Values(
			[105.329761343596, 110.633367329871, 105.708502147323, 103.569269019798, 103.578483185784,
			104.373287001171, 105.263629156484, 104.884528151328, 106.703804152221, 105.664627560655]
		)
	),
	New Column( "SIT1",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property( "Spec Limits", {LSL( 145.48315 ), USL( 185.72141 ), Target( 165.60228 )} ),
		Set Values(
			[148.807936826583, 152.591832248891, 154.568333571174, 118.695148858091, 137.736892539345,
			162.26978689599, 174.520277278376, 138.985120782571, 152.119109194528, 172.00040324347]
		)
	),
	New Column( "INM1",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 57.0276784418165 ), USL( 99.0010932081266 ), Target( 78.0143858249716 )}
		),
		Set Values(
			[77.1728296444077, 82.0469082410668, 83.687085328962, 80.7577994209762, 77.3810009438902,
			86.5969854076827, 83.9227125706495, 84.0696675266069, 75.8130299795504, 80.9676937912243]
		)
	),
	New Column( "INM2",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property(
			"Spec Limits",
			{LSL( 63.8010373764921 ), USL( 65.1798987027106 ), Target( 64.4904680396014 )}
		),
		Set Values(
			[63.5204508646779, 63.3088610076463, 63.867818710853, 62.7780229155406, 59.203474982935,
			65.6340594039682, 68.2292406778111, 64.6692277412529, 62.6014259723118, 68.7977074923828]
		)
	),
	New Column( "VPM1",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Property( "Spec Limits", {LSL( -80.0000000000001 ), USL( -80 ), Target( -80 )} ),
		Set Values( [-80, -80, -80, -80, -80, -80, -80, -80, -80, -80] )
	)
);

// Here is the actual code

// Create a list of the Y columns to be found
YColList = {"Col_Y1", "Col_Y2", "Col_Y3"};
// Get all of the columns in the data table
AllColList = dt << get column names( string );

// Loop across the list of Y columns and sort and stack
For( I = 1, I <= N Items( YColList ), i++,
	startCol = Contains( AllColList, YColList[i] ) + 1;
	If( i == N Items( YColList ),
		endCol = N Col( dt ),
		endCol = Contains( AllColList, Char( YColList[i + 1] ) ) - 1
	);
	targetList = {};
	For( t = startCol, t <= endCol, t++,
		Insert Into( targetList, AllColList[t] )
	);

	jdt = dt << Sort( By( Eval List( targetList ) ) );

	edt = jdt << Stack(
		columns( Eval List( targetList ) ),
		Source Label Column( "Label_bh_bt" ),
		Stacked Data Column( "Data_bh_bt" )
	);
);