split an interval in 15 min sub-intervals

[#1]

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

[antoniotiman]

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

[#3]

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:

startTime	endTime	time start	time end	duration (min)	units	no. of 15 min intervals
				35	10.28	4
10:28:31 AM	11:03:43 AM	07:37:52	07:45:00	35	10.28	4
10:28:31 AM	11:03:43 AM	07:45:00	08:00:00	35	10.28	4
10:28:31 AM	11:03:43 AM	08:00:00	08:15:00	35	10.28	4
10:28:31 AM	11:03:43 AM	08:15:00	08:21:38	35	10.28	4
10:28:31 AM	11:03:43 AM	22:07:48	22:15:00	35	10.28	4
10:28:31 AM	11:03:43 AM	22:15:00	22:30:00	35	10.28	4
10:28:31 AM	11:03:43 AM	22:30:00	22:45:00	35	10.28	4
10:28:31 AM	11:03:43 AM	22:45:00	23:00:00	35	10.28	4
10:28:31 AM	11:03:43 AM	23:00:00	23:15:00	35	10.28	4
10:28:31 AM	11:03:43 AM	23:15:00	23:30:00	35	10.28	4
10:28:31 AM	11:03:43 AM	23:30:00	23:45:00	35	10.28	4

.......

[antoniotiman]

Can You share Your input data ?

[#5]

Thank you for the quick reply Antonio,

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

startTime	endTime	UID	Machine_ID	Production
2017-07-01 10:28:31	2017-07-01 11:03:43	BBB	AAA	45

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...

startTime	endTime	UID	Machine_ID	Production
2017-07-01 10:28:31	2017-07-01 10:43:00	BBB	AAA	15

2017-07-01 10:43:00

2017-07-01 10:58:00

BBB

AAA

15

2017-07-01 10:58:00

2017-07-01 11:03:00

BBB

AAA

5

[antoniotiman · Accepted solution]

See Attachment

[MarcoWedel]

can you elaborate on your production numbers?

How are 45 split in 15+15+5?

thanks

Marco

[antoniotiman]

If Production is % of Interval Time

[#9]

Thank you Antonio,

works like a charm!

best,

Alex

[#10]

Hi Marco,

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

best,

Alex