Place values from List into data table

terapin — Mon, 28 Nov 2016 22:20:53 GMT

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
 ),
);

Re: Place values from List into data table

Wendy_Murphrey — Tue, 29 Nov 2016 15:43:42 GMT

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

Re: Place values from List into data table

terapin — Tue, 29 Nov 2016 23:11:05 GMT

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.

topic Re: Place values from List into data table in Discussions

Place values from List into data table

Re: Place values from List into data table

Re: Place values from List into data table