Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See Attachment
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
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 |
.......
Can You share Your input data ?
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 |
See Attachment
can you elaborate on your production numbers?
How are 45 split in 15+15+5?
thanks
Marco
If Production is % of Interval Time
Thank you Antonio,
works like a charm!
best,
Alex
Hi Marco,
last interval is 5 min only so will produce less (5 instead of 15 produced in 15 min).
best,
Alex