Locate latest max value in list for each day

[#1]

I'm determining the maximum values for a given day and I'm running into a problem when there are multiple max values for a given day in my list.  I can't figure out how to locate and create a new of just the latest max value and its corresponding date time for a given day when more than one max values exist for that day.  Any suggestions on how to accomplish this?

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]

  )

  )

);

Wait( 2 );

Get the highest growth for each day and locate the corresponding datetime

Summarize( growth_date_list = by( dt_growth:Date ), g = Max( dt_growth:Growth ) );

max_growth_list = As List( g );

show(growth_date_list);

show(max_growth_list);

//locate rows with max growth, excluding those dates with all missing data

rows = dt_growth << get rows where(

  And(

  !Is Missing( :growth ),

  Contains( max_growth_list, :Growth ),

  Contains( max_growth_list, :Growth ) == Contains( growth_date_list, Format( :Date, "m/d/y" ) )

  )

);

// Select only the latest max value for a given day when multiple identical values exist and create new list of just those daily max values and their datetime

dt_growth << Select Rows ( rows );

[#2]

Interesting problem.  If I understand it right, try adding these three formula columns; "theRow" will hold the row with the last maximum value.  (aRow is not the one you want.) 

    New Column( "maxGrowth",

        Numeric,

        "Continuous",

        Format( "Best", 12 ),

        Formula( Col Maximum( :Growth, :Date ) )

    ),

    New Column( "aRow",

        Numeric,

        "Continuous",

        Format( "Best", 12 ),

        Formula( (:Growth == :maxGrowth) * Row() )

    ),

    New Column( "theRow",

        Numeric,

        "Continuous",

        Format( "Best", 12 ),

        Formula( Col Maximum( :aRow, :Date ) )

    )

then a summary Data Table( "Growth" ) << Summary( Group( :theRow ) )

[#3]

Thanks Craige for your reply,

Unfortunately, what you've proposed doesn't quite work for me.  I'm creating a list of the max values by day.  I will do some additional work with the contents of the list.  My issue is that there are certain days that contain multiple instances of the same value.  I need to create a list then that has just the latest datetime occurrence of the daily max value, not all of them.

[#4 · Accepted solution]

Maybe something like this? I learned the rather neat 'Row' trick from Brady Brady I think.

NamesDefaultToHere(1);

// Make some data

dt = NewTable("Multiple Max",

NewColumn("Group", Numeric, Nominal, Formula(RandomInteger(1, 3))),

NewColumn("Values", Numeric, Comtinuous, Formula(RandomInteger(1, 6))),

AddRows(100)

);

dt << runFormulas;

Column(dt, "Group") << deleteFormula;

Column(dt, "Values") << deleteFormula;

dt << Sort(By(:Group), ReplaceTable);

// Define two useful columns (later deleted)

dt << New Column( "Row", formula( Row() ) );

dt << NewColumn("Indicator", Numeric, Nominal, Formula(If(:Values[Row()] == Col Maximum(:Values, :Group), 1)));

// Make an invisible summary table

dt2 = dt << Summary(Group(:Group, :Indicator), Max(:Row));

iVals = Column(dt2, "Indicator") << getValues;

mVals = Column(dt2, "Max(Row)") << getValues;

rn = mVals[Loc(iVals == 1)];

Close(dt2, NoSave);

// Delete the auxiliary columns

dt << deleteColumns({:Row, :Indicator});

ClearLog();

Print(rn);

[#5]

Ian,

Thanks for your suggestion.  Although Craige's suggestion worked as well, yours did a fantastic job of putting the items I need for additional calculations into lists.  I learned a lot from your (Brady Brady's) suggestion about the absolute power of Associative Arrays. 

[#6]

Craige,

I think I was too quick to dismiss your suggestion.  After playing with your example more I see that it does in fact do what I was hoping for.  Sorry to be so quick in dismissing your suggestion previously. I ended up using Ian's suggestion instead since it put the items I needed into lists.

[#7]

You might try replacing the last line of your code (where you are selecting all the rows in the "rows" matrix) with the following loop. It just compares the Date for each index in rows with the next one and only selects the row if they are not equal. The last line of code selects the last row in "rows" since there is nothing to compare it to. Hope this is what you are after (the assumption is that the table is sorted by Date & Time as in your example).

For( i = 1, i <= N Rows( rows ) - 1, i++,

If( :Date[rows] != :Date[rows[i + 1]],

  dt_growth << Select Rows( rows )

)

);

dt_growth << Select Rows( rows );