8 Replies Latest reply: Jan 30, 2013 10:21 AM by Ralph Graham RSS

    Grouped by problem

      Hello,

       

      I'm difficulty trying to generate a logic in order to meet a current requirement. I have a table which has a number of transactions which are stored with a timestamp and I am trying to create a min (start time) and max (finish time) per group of transactions. Based on the format of the attached spreadsheet I simply put a min and max around the timestamp field and grouped by Site, material, Date, Shift and Line and this essentially generated the start and end time of the transactional run.

       

      The issue I have encountered is (and the attached spreadsheet shows this) is that the same material can go down the same line on the same shift more than once and this essentially skews the max and min times as I would like to show this as a seperate group of transactions, i.e. I want the end result to be like the below table

       

      SitematerialDateShiftLineMin TimeMax Time
      Test1Product112/01/2013DaysLine112:45:1212:52:21
      Test1

      Product2

      12/01/2013

      DaysLine113:32:4513:54:56
      Test1Product112/01/2013DaysLine114:01:1214:06:56
      Test1Product312/01/2013NightsLine215:32:1115:50:24

       

      If I used my current logic there would only be 3 rows with Product1 only appearing once with a min time of 12:45:12 and max time of 14:06:56. Does anyone know of any logic that could be implemented in the script to generate the above table?

       

      Thanks,

       

      Ralph

        • Re: Grouped by problem

          Hi,

           

          As far as i understood you should create two different expressions with Max() and Min() functions. But if you use the timestamp column without any functions it will still duplicate the row. Therefore your date format should be like your Date column in the table you shared above.

           

          Best.

            • Re: Grouped by problem

              Hello,

               

              I am currently using max and min on the timestamp and while it works in 95% of scenarios it does not work when there are multiple transaction runs of the same material (in my attached example Product1). Essentially I am trying to show how long each transaction run lasts for using the max and min values but it will not show correct data when multiple runs of the same material.

               

              Thanks,

               

              Ralph

            • Re: Grouped by problem
              Dave Riley

              Hi Ralph,

               

               

              You probably want to create a unique reference that relates to each material pass down the line in the load script. Once your data is in the correct chronological order, you can inspect it as follows, incrementing the pass value every time it comes across a change in material ...

               

              Data:

              Load

                  *,

                  Alt(peek(pass),0) + if(material<>peek(material),1,0) as pass

              inline [

              Site,    material,    timestamp,    Date,    Shift,    Line

              Test1,    Product1,    12:45:12,    12/01/2013,    Days,    Line1

              Test1,    Product1,    12:52:21,    12/01/2013,    Days,    Line1

              Test1,    Product2,    13:32:45,    12/01/2013,    Days,    Line1

              Test1,    Product2,    13:54:56,    12/01/2013,    Days,    Line1

              Test1,    Product1,    14:01:12,    12/01/2013,    Days,    Line1

              Test1,    Product1,    14:06:56,    12/01/2013,    Days,    Line1

              Test1,    Product3,    15:32:11,    12/01/2013,    Nights,    Line2

              Test1,    Product3,    15:50:24,    12/01/2013,    Nights,    Line2];

               

              The Alt(peek(pass),0) bit seeds the pass reference at zero.

               

              flipside

               

               

              PS Just then add "pass" as a dimension in your chart table.

                • Re: Grouped by problem

                  Hi flipside,

                   

                  Thanks for the reply. I have just figured out that my qvd data is not sorted in chronological order and I can't go back to the base sql query. Do you know of a way to sort the data in the script by chronlogical order?

                   

                  Thanks.

                   

                  Ralph

                    • Re: Grouped by problem
                      Mark Sheraton

                      TEMP:

                      LOAD Site,

                           material,

                           timestamp,

                           Date,

                           Shift,

                           Line

                      FROM

                      [YourQVD.qvd] (qvd);

                       

                       

                      NoConcatenate

                      A:

                      LOAD *

                      Resident TEMP

                      order by Date, timestamp

                      ;

                       

                       

                      DROP TABLE TEMP;

                        • Re: Grouped by problem

                          Hello,

                           

                          Thanks for that. I have used that in the past to sort data but is not working this time (for information my timestamp field does contain the date as well). Could it be bad formatting of the timestamp?

                           

                          Thanks,

                           

                          Ralph

                            • Re: Grouped by problem
                              Dave Riley

                              Hi Ralph,

                               

                              Yes, it COULD be bad formatting of the timestamp, but also consider that you may have to allow for the same material going down separate lines with overlapping time spans.  In this case it may be wise to sort the data by Line first, then timestamp. The same thing could also happen with Shifts in theory, this all depends on whether you are able to have an overrunning shift and how you classify that possibility.

                               

                              My end script currently looks like this ...

                               

                              OrigData:

                              Load *

                              inline [

                              Site,    material,    timestamp,    Date,           Shift,   Line

                              Test1,    Product1,    12:45:12,    12/01/2013,    Days,    Line1

                              Test1,    Product1,    12:52:21,    12/01/2013,    Days,    Line1

                              Test1,    Product1,    14:00:00,    12/01/2013,    Days,    Line2

                              Test1,    Product1,    14:01:12,    12/01/2013,    Days,    Line1

                              Test1,    Product1,    14:06:56,    12/01/2013,    Days,    Line1

                              Test1,    Product1,    14:10:00,    12/01/2013,    Days,    Line2

                              Test1,    Product2,    13:32:45,    12/01/2013,    Days,    Line1

                              Test1,    Product2,    13:54:56,    12/01/2013,    Days,    Line1

                              Test1,    Product3,    15:32:11,    12/01/2013,    Nights,  Line2

                              Test1,    Product3,    15:50:24,    12/01/2013,    Nights,  Line2];

                               

                              Temp:

                              Noconcatenate LOAD Site,

                                   material,

                                   timestamp,

                                   Date,

                                   Shift,

                                   Line

                              resident OrigData;

                               

                              drop table OrigData;

                               

                              Data:

                              noconcatenate Load

                                   Site,

                                   material,

                                   timestamp,

                                   Date,

                                   Shift,

                                   Line,

                                  Alt(peek(pass),1000) + if(material&Line&Shift<>peek(material)&peek(Line)&peek(Shift),1,0) as pass

                              resident Temp order by Line, Date, timestamp;

                               

                              DROP TABLE Temp;

                               

                               

                              flipside

                        • Re: Grouped by problem

                          Thanks for that, that actually sorts my data ok. I will try and implement the pass logic on my actual qvd although having thought about it I'm not sure what I want to do will be possible.

                           

                          Update:

                          I was able to apply this logic to my application, thanks very much.