how to find the max values with in a select hour

[#1]

I have a data file contain data for 5min interval. But my selection is at hour level. So i want to find out the max number with in selection hour. How can i do this using set analysis.

thanks for your help. .

Search_Date	search_Total	Search_Fail
7/18/12 9:40 AM	209377.58	825.50
7/18/12 9:45 AM	209327.42	1009.38
7/18/12 9:50 AM	206869.36	1093.96
7/18/12 9:55 AM	206939.98	883.38
7/18/12 10:00 AM	205994.86	988.92
7/18/12 10:05 AM	208033.28	813.70
7/18/12 10:10 AM	208699.00	751.00
7/18/12 10:15 AM	207005.09	904.78
7/18/12 10:20 AM	204287.41	865.90
7/18/12 10:25 AM	203422.14	783.50
7/18/12 10:30 AM	201387.82	833.79
7/18/12 10:35 AM	200821.00	853.00
7/18/12 10:40 AM	201822.52	741.46
7/18/12 10:45 AM	199516.96	700.64
7/18/12 10:50 AM	200198.02	916.40
7/18/12 10:55 AM	201547.42	894.90

[Oleg_Troyansky · Accepted solution]

Let's assume that you let users select a date (call it SelDate) and an hour (call it SelHour), where the hour is a number between 1 and 24. Then, your expression could look like this:

max({<Search_Date={">=$(=timestamp(only(SelDate) + only(SelHour)/24 ))<$(=timestamp(only(SelDate) + (only(SelHour)+1)/24 ))"}>}   search_Total)

This formula contains several Set Analysis concepts:

   - using seach (signified by double quotes "")

   - using $-sign expansions to calculate the top of the hour and the top of the next hour

   - how to use Set Analysis for dates and timestamps, which is always a bit tricky

I'll be covering those concepts in detail in my upcoming class for Set Analysis. If you are interested to lear more about set analysis, follow my LinkedIn or Twitter updates.

Cheers,

@OlegTroyansky

[nagaiank]

If you want to get your results using script, the following script will work:

Data:
LOAD *,Text(Date(Timestamp#(Search_Date,'M/DD/YY h:mm TT'),'MM/DD/YYYY hh')) as DateHH;
LOAD * Inline [
Search_Date,Search_Total,Search_Fail
7/18/12 9:40 AM,209377.58,825.50
7/18/12 9:45 AM,209327.42,1009.38
7/18/12 9:50 AM,206869.36,1093.96
7/18/12 9:55 AM,206939.98,883.38
7/18/12 10:00 AM,205994.86,988.92
7/18/12 10:05 AM,208033.28,813.70
7/18/12 10:10 AM,208699.00,751.00
7/18/12 10:15 AM,207005.09,904.78
7/18/12 10:20 AM,204287.41,865.90
7/18/12 10:25 AM,203422.14,783.50
7/18/12 10:30 AM,201387.82,833.79
7/18/12 10:35 AM,200821.00,853.00
7/18/12 10:40 AM,201822.52,741.46
7/18/12 10:45 AM,199516.96,700.64
7/18/12 10:50 AM,200198.02,916.40
7/18/12 10:55 AM,201547.42,894.90
];
Result:
LOAD DateHH, Max(Search_Total) as MaxTotal, Max(Search_Fail) as MaxFail
Resident Data Group By DateHH;