4 Replies Latest reply: Dec 6, 2014 9:49 AM by Marcus Ian

# Find specific time associated with max/min value

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

• ###### Re: Find specific time associated with max/min value

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;

• ###### Re: Find specific time associated with max/min value

can you please elaborate a bit more?

thanks

regards

Marco

• ###### Re: Find specific time associated with max/min value

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.

• ###### Re: Find specific time associated with max/min value

Try This:

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

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

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

DROP TABLE tab1;

tab3: