Find specific time associated with max/min value

[#1]

Hi,

I have the following table:

Time     High     Low

...

11         7          1 

12         5          2

13         3          1

14         6          3 

...

How do you find the time when maximum high and minimum low occurred between, for example, time 11 and 14?

Many thanks

Marcus

[MK_QSL]

T1:

LOAD *, High&Low as Key Inline

[

  Time,     High,     Low

  11,         7,          1

  12,         5,          2

  13,         3,          1

  14,         6,          3

];

Left Join (T1)

Load Max(High) & Min(Low) as Key2 Resident T1;

T2:

Load Time as RequestedTime Resident T1 Where Key = Key2; 

DROP Fields Key, Key2;

[MarcoWedel]

can you please elaborate a bit more?

thanks

regards

Marco

[alkesh_sharma]

Try This:

T1:
LOAD * Inline
[
Time,     High,     Low
11, 7,          1
12, 5,          2
13, 3,          1
14, 6,          3
];

tab1:
LOAD
Max(High) AS Max_High,
Min(Low) AS Min_Low
RESIDENT T1;

tab2:
MAPPING LOAD
Max_High,
1 AS Flag
RESIDENT tab1;
tab3:
MAPPING LOAD
Min_Low,
1 AS Flag
RESIDENT tab1;

DROP TABLE tab1;

tab3:
LOAD *
WHERE Max_high = 1 OR Min_Low = 1;
LOAD Time,
High,
Low,
ApplyMap('tab2',High,0) AS Max_high,
ApplyMap('tab3',Low,0) AS Min_Low
RESIDENT T1;
DROP TABLE T1;        

[#5]

Sure. Basically I have two tables as follows:

Table 1

Date            Hour     High     Low

...

1/12/2014     11         7          1

1/12/2014     12         5          2

1/12/2014     13         3          1

1/12/2014     14         6          3

...

Table 2

Timezone     Hour

...

London         8

London         9

London         10

London         11

NY               12

NY               13

NY               14

......

I would like to find the Hours when the highest and lowest values are for everyday during the NY timezone.