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

    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
          Manish Kachhia

          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
            Marco Wedel

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