Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Tags (3)
1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Grouped by problem

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

8 Replies
Not applicable

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.

Not applicable

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

flipside
Valued Contributor II

Re: Grouped by problem

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.

Not applicable

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

msheraton
Contributor III

Re: Grouped by problem

TEMP:

LOAD Site,

     material,

     timestamp,

     Date,

     Shift,

     Line

FROM

[YourQVD.qvd] (qvd);

NoConcatenate

A:

LOAD *

Resident TEMP

order by Date, timestamp

;

DROP TABLE TEMP;

Not applicable

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

flipside
Valued Contributor II

Re: Grouped by problem

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

Not applicable

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.

Community Browser