Grouped by problem

[#1]

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

Site	material	Date	Shift	Line	Min Time	Max Time
Test1	Product1	12/01/2013	Days	Line1	12:45:12	12:52:21
Test1	Product2	12/01/2013	Days	Line1	13:32:45	13:54:56
Test1	Product1	12/01/2013	Days	Line1	14:01:12	14:06:56
Test1	Product3	12/01/2013	Nights	Line2	15:32:11	15: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

[#2]

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.

[#3]

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

[flipside]

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.

[#5]

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

[RedSky001]

TEMP:

LOAD Site,

     material,

     timestamp,

     Date,

     Shift,

     Line

FROM

[YourQVD.qvd] (qvd);

NoConcatenate

A:

LOAD *

Resident TEMP

order by Date, timestamp

;

DROP TABLE TEMP;

[#7]

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

[flipside · Accepted solution]

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

[#9]

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.