16 Replies Latest reply: Feb 3, 2017 11:23 AM by Eduardo DImperio RSS

    Help with Group By

    Eduardo DImperio

      Hi Everyone,

       

      I have a litle problem here. I have some values by hour and i need to get the lower hour (by hour)

      Like

       

      Input

      1:00 am --- 100

      1:03 am --- 120

      1:50 am --- 190

      2:02 am --- 200

      2:27 am --- 230

      2:45 am --- 280

       

      output

       

      1:00 am --- 100

      2:02 am --- 200

       

      I have this code:

       

      LOAD

          DATE_READ,

          VALUE_READ,

          ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

      RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

       

      And tried this one, withou sucess.

       

      LOAD

          DATE_READ,

          VALUE_READ,

          MIN(DATE_READ) AS MENOR_VALOR

      RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2

      GROUP BY DATE_READ,VALUE_READ;

       

      Someone could help me?

       

      Thanks

        • Re: Help with Group By
          Sunny Talwar

          What is the error you get when you use the script?

           

          LOAD

              DATE_READ,

              VALUE_READ,

              MIN(DATE_READ) AS MENOR_VALOR

          RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2

          GROUP BY DATE_READ,VALUE_READ;

          • Re: Help with Group By
            Marcus Sommer

            You could try something like this:

             

            Output:

            load

                 date(floor(DATE_READ)) as Date, time(frac(DATE_READ)) as Time,

                 hour(frac(DATE_READ)) as Hour, VALUE_READ

            RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

             

            inner join (Output)

             

            load Date, Hour, min(VALUE_READ) as MinVALUE_READ

            resident Output group by Date, Hour;

             

            - Marcus

              • Re: Help with Group By
                Eduardo DImperio

                Hi Marcus,

                 

                Thanks for your time !

                 

                I need the min Date group by hour and not value read. Sometimes, my measure 1:00 could be higher that 1:05

                  • Re: Help with Group By
                    Marcus Sommer

                    Then try it with some slight adjustements, something like this:

                     

                    Output:

                    load

                         date(floor(DATE_READ)) as Date, time(frac(DATE_READ)) as Time,

                         hour(frac(DATE_READ)) as Hour, VALUE_READ

                    RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

                     

                    inner join (Output)

                     

                    load Date, Hour, min(Time) as MinTime

                    resident Output group by Date, Hour;

                     

                    - Marcus

                      • Re: Help with Group By
                        Eduardo DImperio

                        Hi Marcus,

                         

                        Unfortunately not worked, so i tried this code:

                         

                        Input:

                        load

                        Min(VALUE_READ),

                        FLOOR(DATE_READ,0.1)

                        //     ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,

                        RESIDENT CONCAT_TABLE

                          where DATE_READ>=TODAY()-2

                             group by DATE_READ;

                         

                        with this result

                        Data_Erro.JPG

                        And i dont understant why min(value_read), doesn't get only the small value per date

                          • Re: Help with Group By
                            Eduardo DImperio

                            I did another code, more clean i think

                             

                            Input:

                             

                             

                            load

                            VALUE_READ,

                            FLOOR(DATE_READ,000.1) AS DATA

                            RESIDENT CONCAT_TABLE

                              where DATE_READ>=TODAY()-2;

                              

                             

                             

                            Output:

                            load

                            DISTINCT

                            MIN(VALUE_READ),

                            DATA

                            RESIDENT Input

                                group by DATA;

                             

                            DROP TABLE Input;

                             

                            I thought that my code would group by date and for each one pick the smallest value, but in the end it just picked the smallest value of all dates and replied

                             

                             

                             

                            Data_Erro.JPG

                          • Re: Help with Group By
                            Marcus Sommer

                            There are another slight adjustments necessary regarding to the format and the field-name of the min(Time):

                             

                            Output:

                            load

                                today() as Date, time(time#(subfield(Time, ' ', 1), 'hh:mm')) as Time,

                                hour(time#(subfield(Time, ' ', 1), 'hh:mm')) as Hour, Value, Time as TimeOrigin

                            inline [

                            Time, Value

                            1:00 am, 100

                            1:03 am, 120

                            1:50 am, 190

                            2:02 am, 200

                            2:27 am, 230

                            2:45 am, 280

                            ];

                             

                            inner join (Output)

                             

                            load Date, Hour, time(min(Time)) as Time

                            resident Output group by Date, Hour;

                             

                            The inline-statement is only to have data for it and you won't need it in your load - only the methods to split the timestamp per frac() and floor() and then formatting them (and use only a pure floor() without a second parameter like 0.1 which won't create a date else just round the timestamp).

                             

                            - Marcus

                      • Re: Help with Group By
                        Eduardo DImperio

                        I used the Marcus idea and change a little the code, now i understand the problem but dont know how to solve it.

                        If i dont use VALUE_READ in the group by i get no value and if i use i get more then one.

                         

                        Any Ideia?

                         

                        OUTPUT:

                        load

                         

                        Floor(DATE_READ) as DATA,

                        Hour(DATE_READ) as HORA,  

                        VALUE_READ,

                        ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

                        RESIDENT CONCAT_TABLE

                          where DATE_READ>=TODAY()-2;

                         

                        inner join (OUTPUT)

                         

                        load

                        DATA,

                        HORA,

                        Min(VALUE_READ) as VALOR

                        resident OUTPUT

                        group by DATA,HORA;

                         

                        DROP TABLE CONCAT_TABLE;

                         

                         

                        Data_Erro_1.JPG

                         

                        OUTPUT:

                        load

                         

                        Floor(DATE_READ) as DATA,

                        Hour(DATE_READ) as HORA,   

                        VALUE_READ,

                        ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

                        RESIDENT CONCAT_TABLE

                          where DATE_READ>=TODAY()-2;

                         

                        inner join (OUTPUT)

                         

                        load

                        DATA,

                        HORA,

                        VALUE_READ,

                        Min(VALUE_READ) as VALOR

                        resident OUTPUT

                        group by DATA,HORA,VALUE_READ;

                         

                         

                         

                         

                        DROP TABLE CONCAT_TABLE;

                        Data_Erro_2.JPG

                          • Re: Help with Group By
                            Marcus Sommer

                            I will try to simplify the approach from above and leave here the various transformations on the timestamp and the exact where-clause out:

                             

                            Output:

                            load Date, Hour, Time, Value from Source where 1=1;

                             

                            inner join (Output)

                             

                            load Date, Hour, time(min(Time)) as Time

                            resident Output group by Date, Hour;

                             

                            whereby the first part is a normal table-load (with some transformations and a where-clause). The second part loads the data from the first part grouped by Date and Hour so that this load will have max. 48 records (24 hours * 2 days) and min(Time) will return the min. Time for each of these day-hours-buckets. Aften this happens the join-statement which is an inner-join which meant that only those records that have a matching on all key-fields in both tables are left - an inner-join worked therefore like a where-clause and the conditions comes from an outside table-load.

                             

                            Important by this approach are the (values within) the key-fields which are Date, Hour and Time - which you missed in your load-statement.

                             

                            - Marcus

                              • Re: Help with Group By
                                Eduardo DImperio

                                Hi Marcus, I didn't missed, but not worked for the first time or i dont quite understand.

                                and i still dont understand why my code doesn't work, if i have the same date, same hour and diferent values in VALUE_READ, why Min() doesn't work, what i missing?

                                 

                                Below your code adapted to my 2 variable DATE_READ(with contains the date of measures) and VALUE_READ(value read in the meter).

                                 

                                Again thank you for spend yout time to teaching me

                                 

                                Output:

                                load

                                Date(DATE_READ) AS DATA,

                                Hour(DATE_READ) AS HORA,

                                Time(DATE_READ) AS TEMPO,

                                VALUE_READ resident CONCAT_TABLE

                                where DATE_READ>=TODAY()-2;

                                 

                                inner join (Output)

                                 

                                load

                                DATA,

                                HORA,

                                time(min(TEMPO)) as TEMPO

                                resident Output group by DATA, HORA;

                                 

                                DROP TABLE CONCAT_TABLE;

                                Exit Script;

                                 

                                Data_Erro.JPG

                                  • Re: Help with Group By
                                    Marcus Sommer

                                    Without the use of the floor/frac-function remain these fields timestamps like 42767,5422128472 for 01.02.2017  13:00:47 and date/time will just format it. Therefore try this:

                                     

                                    Output:

                                    load

                                    Date(floor(DATE_READ)) AS DATA,

                                    Hour(DATE_READ) AS HORA,

                                    Time(frac(DATE_READ)) AS TEMPO,

                                    VALUE_READ resident CONCAT_TABLE

                                    where DATE_READ>=TODAY()-2;

                                     

                                    inner join (Output)

                                     

                                    load

                                    DATA,

                                    HORA,

                                    time(min(TEMPO)) as TEMPO

                                    resident Output group by DATA, HORA;

                                     

                                    DROP TABLE CONCAT_TABLE;

                                    Exit Script;

                                     

                                    - Marcus

                                      • Re: Help with Group By
                                        Eduardo DImperio

                                        Hi Marcus,

                                         

                                        I made some adjustments and finally it works.

                                        Follow the final code and very thanks

                                         

                                         

                                        LOAD

                                            VALUE_READ,

                                            ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,

                                            Date(floor(DATE_READ)) AS DATA,

                                            Time(frac(DATE_READ)) AS TEMPO

                                        RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

                                        //************************************OK

                                        Output:

                                        load

                                        Date(floor(DATE_READ)) AS DATA,

                                        Hour(DATE_READ) AS HORA,

                                        Time(frac(DATE_READ)) AS TEMPO,

                                        ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,

                                        VALUE_READ

                                        resident CONCAT_TABLE

                                        where DATE_READ>=TODAY()-2;

                                         

                                        inner join (Output)

                                         

                                        load

                                        DATA,

                                        HORA,

                                        ID_LEITURA,

                                        time(min(TEMPO)) as TEMPO

                                        resident Output group by DATA, HORA,ID_LEITURA;

                                        // //***************************************OK

                                        NoConcatenate

                                        VALOR:

                                        LOAD

                                        DATA,

                                        HORA,

                                        TEMPO,

                                        ID_LEITURA,

                                        Min(VALUE_READ) AS VALUE_READ

                                        RESIDENT Output

                                        Group By

                                        DATA,

                                        HORA,

                                        TEMPO,

                                        ID_LEITURA

                                        ;

                                        Inner Join(TMP2)

                                        Load

                                        ID_LEITURA,

                                        DATA,

                                        VALUE_READ,

                                        TEMPO

                                        RESIDENT VALOR;

                                        // // //**************************************

                                         

                                         

                                        //DROP TABLE CONCAT_TABLE;

                                        DROP TABLE Output;

                                        DROP TABLE VALOR;