Column reference and sort/Stack

Community Trekker

Joined:

Dec 27, 2016

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{} ?

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions

Super User

Joined:

Jun 22, 2012

Solution

See if this helps you out

``````Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Subset of semiconductor capability",
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" )
);
);``````
Jim
2 REPLIES

Super User

Joined:

Jun 22, 2012

Solution

See if this helps you out

``````Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Subset of semiconductor capability",
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" )
);
);``````
Jim

Community Trekker

Joined:

Dec 27, 2016

That was perfect. Thank you txnelson :)