Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

split an interval in 15 min sub-intervals

Hello,

I tried to solve a Qlik mystery without success, that's why i am trying to find an answer here. Thank you in advance for helping.

I have some production data from a machine, split into intervals of work.

Let's  say from 9:00 to 10:25 the machine produced 10 products.

How can I split the production in 15 min intervals of equal value of output?

Start     Stop     Production

9:00     9:15     1

9:15     9:30     1

9:30     9:45     1

...

you got the idea...

thank you so much,

Alex

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

12 Replies
antoniotiman
Master III
Master III

Hi Alex

LOAD Time(Start+(IterNo()-1)*15/1440,'hh:mm') as Start,
Time(Start+(IterNo())*15/1440,'hh:mm') as Stop,1 as Production Inline [
Start,Stop
9:00,10:25]

While Start+(IterNo()-1)*15/1440 <= Stop
;

Regards,

Antonio

Not applicable
Author

Hi Antonio,

Thank you very much for helping. I tried in many way (some similar with your proposal) but I am doing something wrong...

so:

///// LOAD DATA

Load

startTime,

    endTime,

    QTY....

/// SPLIT INTO 15 MIN INTERVALS by Machine_ID

LOAD

Timestamp(startTime,'hh:mm:ss') as [time start],

Timestamp(endTime,'hh:mm:ss') as [time end],

interval(endTime-startTime,'s') as [Total Seconds],

Machine_ID;

LOAD

*,

if(tStart > TZStart, tStart, TZStart) as startTime,

if(tEnd < TZEnd, tEnd, TZEnd) as endTime ;

LOAD card, Charger, tStart, tEnd,

timestamp(floor(tStart,15/1440)+(iterno()-1)*15/1440) as TZStart,

timestamp(floor(tStart,15/1440)+iterno()*15/1440) as TZEnd

Resident Data

while floor(tStart,15/1440)+(iterno()-1)*15/1440 <= floor(tEnd,15/1440);

.... and this is what i obtain (totally wrong). I should have 3 intervals of 15 min each...


10:28          10:43

10:43          10:58

10:58          11:03

  

but instead i get this:

startTimeendTimetime starttime endduration (min)unitsno. of 15 min intervals
3510.284
10:28:31 AM11:03:43 AM07:37:5207:45:003510.284
10:28:31 AM11:03:43 AM07:45:0008:00:003510.284
10:28:31 AM11:03:43 AM08:00:0008:15:003510.284
10:28:31 AM11:03:43 AM08:15:0008:21:383510.284
10:28:31 AM11:03:43 AM22:07:4822:15:003510.284
10:28:31 AM11:03:43 AM22:15:0022:30:003510.284
10:28:31 AM11:03:43 AM22:30:0022:45:003510.284
10:28:31 AM11:03:43 AM22:45:0023:00:003510.284
10:28:31 AM11:03:43 AM23:00:0023:15:003510.284
10:28:31 AM11:03:43 AM23:15:0023:30:003510.284
10:28:31 AM11:03:43 AM23:30:0023:45:003510.284

.......

antoniotiman
Master III
Master III

Can You share Your input data ?

Not applicable
Author

Thank you for the quick reply Antonio,

A machine is producing 50 units in the 35 minute interval.

startTime endTime UID Machine_IDProduction
2017-07-01 10:28:312017-07-01 11:03:43BBBAAA45


I need to express the below splitting in 15 minutes intervals (and represent it in a chart); of course in the end i need to sum all the Machines and Users for a long period of time...


startTimeendTimeUIDMachine_IDProduction
2017-07-01 10:28:312017-07-01 10:43:00BBBAAA15

2017-07-01 10:43:002017-07-01 10:58:00BBBAAA15

2017-07-01 10:58:002017-07-01 11:03:00BBBAAA5
antoniotiman
Master III
Master III

See Attachment

MarcoWedel

can you elaborate on your production numbers?

How are 45 split in 15+15+5?

thanks

Marco

antoniotiman
Master III
Master III

If Production is % of Interval Time

Not applicable
Author

Thank you Antonio,

works like a charm!

best,

Alex

Not applicable
Author

Hi Marco,

last interval is 5 min only so will produce less (5 instead of 15 produced in 15 min).

best,

Alex