Subscribe Bookmark RSS Feed

Place values from List into data table

terapin

Community Trekker

Joined:

Jun 23, 2011

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
Wendy_Murphrey

Joined:

Jun 23, 2011

Solution

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
2 REPLIES
Wendy_Murphrey

Joined:

Jun 23, 2011

Solution

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

Community Trekker

Joined:

Jun 23, 2011

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.