cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
terapin
Level VI

Place values from List into data table

I have a data table in which I have determined the daily min and max values for a parameter.  After completing some calculations on these list values I want to write the result back to the data table where each daily computation is written to the row corresponding to each change in Date.  Although I have two lists containing the row number of each Date change (rows) and the value to be inserted into that row (adj_growth), I can't seem to figure out how to accomplish this task.  I've tried the following to no avail.  Any suggestions would be greatly appreciated.

 

Column (dt_growth, "adj_growth" ) << Set Values(rows || adj_growth);

 

 

Clear Log();
Names Default To Here(1);
dt_growth = New Table( "Growth",
 New Column( "Date & Time",
 Numeric,
 Continuous,
 Format( "m/d/y h:m", 12 ),
 Input Format( "m/d/y h:m" ),
 Set Values(
 [3225884400, 3225888000, 3225891600, 3225895200, 3225898800,
 3225902400, 3225906000, 3225909600, 3225913200, 3225916800,
 3225920400, 3225924000, 3225927600, 3225931200, 3225934800,
 3225938400, 3225942000, 3225945600, 3225949200, 3225952800,
 3225956400, 3225960000, 3225963600, 3225967200, 3225970800,
 3225974400, 3225978000, 3225981600, 3225985200, 3225988800,
 3225992400, 3225996000, 3225999600, 3226003200, 3226006800,
 3226010400, 3226014000, 3226017600, 3226021200, 3226024800,
 3226028400, 3226032000, 3226035600, 3226039200, 3226042800,
 3226046400, 3226050000, 3226053600, 3226057200, 3226060800,
 3226064400, 3226068000, 3226071600, 3226075200, 3226078800,
 3226082400, 3226086000, 3226089600, 3226093200, 3226096800,
 3226100400, 3226104000, 3226107600, 3226111200, 3226114800,
 3226118400, 3226122000, 3226125600, 3226129200, 3226132800,
 3226136400, 3226140000, 3226143600, 3226147200, 3226150800,
 3226154400, 3226158000, 3226161600, 3226165200, 3226168800,
 3226172400, 3226176000, 3226179600, 3226183200, 3226186800,
 3226190400, 3226194000, 3226197600, 3226201200, 3226204800,
 3226208400, 3226212000, 3226215600, 3226219200, 3226222800,
 3226226400, 3226230000, 3226233600, 3226237200, 3226240800,
 3226244400, 3226248000, 3226251600, 3226255200, 3226258800,
 3226262400, 3226266000, 3226269600, 3226273200, 3226276800,
 3226280400, 3226284000, 3226287600, 3226291200, 3226294800,
 3226298400, 3226302000, 3226305600, 3226309200, 3226312800,
 3226316400, 3226320000, 3226323600, 3226327200, 3226330800,
 3226334400, 3226338000, 3226341600, 3226345200, 3226348800,
 3226352400, 3226356000, 3226359600, 3226363200, 3226366800,
 3226370400, 3226374000, 3226377600, 3226381200, 3226384800,
 3226388400, 3226392000, 3226395600, 3226399200, 3226402800,
 3226406400, 3226410000, 3226413600, 3226417200, 3226420800,
 3226424400, 3226428000, 3226431600, 3226435200, 3226438800,
 3226442400, 3226446000, 3226449600, 3226453200, 3226456800,
 3226460400, 3226464000, 3226467600, 3226471200, 3226474800,
 3226478400, 3226482000, 3226485600, 3226489200, 3226492800,
 3226496400, 3226500000, 3226503600, 3226507200, 3226510800,
 3226514400, 3226518000, 3226521600, 3226525200, 3226528800,
 3226532400, 3226536000, 3226539600, 3226543200, 3226546800,
 3226550400, 3226554000, 3226557600, 3226561200, 3226564800,
 3226568400, 3226572000, 3226575600, 3226579200, 3226582800,
 3226586400, 3226590000, 3226593600, 3226597200, 3226600800,
 3226604400, 3226608000, 3226611600, 3226615200, 3226618800,
 3226622400, 3226626000, 3226629600, 3226633200, 3226636800,
 3226640400, 3226644000, 3226647600, 3226651200, 3226654800,
 3226658400, 3226662000, 3226665600, 3226669200, 3226672800,
 3226676400, 3226680000, 3226683600, 3226687200, 3226690800,
 3226694400, 3226698000, 3226701600, 3226705200, 3226708800,
 3226712400, 3226716000, 3226719600, 3226723200, 3226726800,
 3226730400, 3226734000, 3226737600, 3226741200, 3226744800,
 3226748400, 3226752000, 3226755600, 3226759200, 3226762800,
 3226766400, 3226770000, 3226773600, 3226777200, 3226780800,
 3226784400, 3226788000, 3226791600, 3226795200, 3226798800,
 3226802400, 3226806000, 3226809600, 3226813200, 3226816800,
 3226820400, 3226824000, 3226827600, 3226831200, 3226834800,
 3226838400, 3226842000, 3226845600, 3226849200, 3226852800,
 3226856400, 3226860000, 3226863600, 3226867200, 3226870800,
 3226874400, 3226878000, 3226881600, 3226885200, 3226888800,
 3226892400, 3226896000, 3226899600, 3226903200, 3226906800]
 )
 ),
 New Column( "Year", Numeric, Continuous, Format( "Best", 8 ), Formula( Year( :Name( "Date & Time" ) ) ), Lock( 1 ) ),
 New Column( "Month", Numeric, Continuous, Format( "Best", 8 ), Formula( Month( :Name( "Date & Time" ) ) ), Lock( 1 ) ),
 New Column( "Day", Numeric, Continuous, Format( "Best", 8 ), Formula( Day( :Name( "Date & Time" ) ) ), Lock( 1 ) ),
 New Column( "Hour", Numeric, Continuous, Format( "Best", 8 ), Formula( Hour( :Name( "Date & Time" ) ) ), Lock( 1 ) ),
 New Column( "Date", Numeric, Continuous, Format( "m/d/y", 10 ), Formula( Date MDY( :Month, :Day, :Year ) ), Lock( 1 ) ),
 New Column( "Growth",
 Numeric,
 Continuous,
 Format( "Fixed Dec", 12, 4 ),
 Set Values(
 [-0.00100011424000851, -0.000363872799278378, -0.000363871984284188,
 -0.00100005824000851, -0.00227235992075627, -0.00418081408649503, -
 0.00545309175689348, -0.00736156445270497, -0.00736136657166755, -
 0.00545294517616856, -0.00672516584616703, -0.00354446026036948, -
 0.00227219704017202, 0.00154456188025604, 0.00726960356524363,
 0.00790566809993933, 0.0129946043300728, 0.0142672814031039,
 0.0142680804082943, 0.0212680506560791, 0.021269003361861,
 0.0219050687116747, 0.0212692891739123, 0.014270349583592,
 0.0161791280461629, 0.0123615072003508, 0.0219050687116747,
 0.0104520647031608, -0.0022725075312535, -0.00418091710202046,
 0.00663394656359483, 0.0123593477559727, 0.0212654783489747,
 0.0288989636199062, 0.0346241368109538, 0.0435298706416388,
 0.0734286369895607, 0.072790864489845, 0.0632482414590739,
 0.063884648195645, 0.0740617924812754, 0.10524374653983,
 0.105238560337556, 0.106520183190921, 0.095705821744203,
 0.0721597475663495, 0.0823432919531381, 0.0829770846230983,
 0.0944282992395635, 0.106520183190921, ., ., ., ., ., ., ., ., ., .,
 ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,
 ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,
 ., ., ., ., ., ., ., 0.132598446121035, 0.142136100159265,
 0.144047485365236, 0.141510082527217, 0.133243284004832,
 0.136430823506921, 0.129433763704459, ., 0.116070160678918,
 0.115432357842292, 0.117340824227052, 0.114794572105785,
 0.114790201208717, 0.114151689739583, 0.113513458180752,
 0.112872468220974, 0.115423050522635, 0.143405851093052,
 0.138950940416588, 0.140217253219071, 0.144662268859769,
 0.14847441971641, 0.152290783704404, 0.155475963397883,
 0.164426756532424, 0.16760053536469, 0.162514344711853,
 0.159332058795417, 0.16760053536469, 0.149160923755755,
 0.137709192270229, 0.130708924366787, 0.123070306637102,
 0.117974687738857, 0.119245809936872, 0.117326106775782,
 0.122413756226772, 0.130047595709665, 0.145948812468612,
 0.150402142613181, ., ., 0.05, 0.04, 0.045, ., ., ., ., ., ., ., ., .,
 ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,
 ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,
 ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,
 ., 0.41763939536042, 0.415693459004884, 0.411893026189672,
 0.409996367624642, 0.406806168639344, 0.405541937379636,
 0.403607071498291, 0.409315222566001, 0.409944023628439,
 0.409934840470904, 0.413750645246311, 0.417566432921535,
 0.422655385641797, 0.42965365812232, 0.436654896453155,
 0.44364930329344, 0.45064765414928, 0.457646036355387,
 0.467820889024742, 0.481185887340017, 0.493284494492094,
 0.49456564669957, 0.499026564408861, 0.503492132922427,
 0.50285933023531, 0.506056129447622, 0.506702549723624,
 0.506067464368605, 0.507337632217846, 0.504137348451565,
 0.500946283675119, 0.504757712657227, 0.512386052830777,
 0.520015386838913, 0.529555420635595, 0.539096576517502,
 0.553087055836816, 0.567072203175747, 0.581062055491441,
 0.593783392145494, 0.603324019354862, 0.617957789218505,
 0.680681868813513, 0.640872945402754, 0.656807530753031,
 0.659356671575131, 0.660007636808086, 0.665113592325388]
 )
 ),
 
New Column( "Adj_Growth", Numeric, Continuous, Format( "Fixed Dec", 12, 4 ) ),
 
);

Wait( 2 );





// Get the highest and lowest growth for each date
Summarize( growth_date_list = by( dt_growth:Date ), g = Max( dt_growth:Growth ) );
max_growth_list = As List( g );

Summarize( growth_date_list = by( dt_growth:Date ), h = Min( dt_growth:Growth ) );
min_growth_list = As List( h );


//locate rows of date boundaries
rows = dt_growth << get rows where(
 :Name( "Date" ) != Lag( :Name( "Date" ), 1)
);



adj_growth = {};


For( i = 1, i <= N Items( max_growth_list ), i++,
 If ( ( ( (max_growth_list[i] + min_growth_list[i]) / 2) - 0.1 ) < 0 | IsMissing( max_growth_list[i] )| IsMissing (min_growth_list[i] ),
 //Then
 adj_growth[i] = 0,
 // Else
 adj_growth[i] = ((max_growth_list[i] + min_growth_list[i]) / 2) - 0.1
 ),
);
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Place values from List into data table

Hello, 

The results of your code show that there are an unequal number of rows to the number of values in adj_growth: 

N Items(adj_growth) = 13;
N Rows(rows) = 12;

Assuming you intended the first value of adj_growth to appear in row 1, you could assign the values using a For loop.

For( i = 1, i <= N Items( adj_growth ), i++,
	If( i == 1,
		r = 1,
		r = rows[i - 1]
	);
	Column( "Adj_Growth" )[r] = adj_growth[i];
);

Hope that helps!

Wendy

Wendy

View solution in original post

2 REPLIES 2

Re: Place values from List into data table

Hello, 

The results of your code show that there are an unequal number of rows to the number of values in adj_growth: 

N Items(adj_growth) = 13;
N Rows(rows) = 12;

Assuming you intended the first value of adj_growth to appear in row 1, you could assign the values using a For loop.

For( i = 1, i <= N Items( adj_growth ), i++,
	If( i == 1,
		r = 1,
		r = rows[i - 1]
	);
	Column( "Adj_Growth" )[r] = adj_growth[i];
);

Hope that helps!

Wendy

Wendy
terapin
Level VI

Re: Place values from List into data table

Thanks Wendy for your suggestion.  That definitely helped.  As did counting the number of items in my list.  My attempts to write from my list to the data table didn't include N Items in the For Loop.  I only used the name of the list.