topic Re: split an interval in 15 min sub-intervals in QlikView

split an interval in 15 min sub-intervals

Thu, 27 Jul 2017 08:13:07 GMT

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

Re: split an interval in 15 min sub-intervals

antoniotiman — Thu, 27 Jul 2017 17:28:01 GMT

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

Re: split an interval in 15 min sub-intervals

Mon, 31 Jul 2017 10:15:10 GMT

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

.......

Re: split an interval in 15 min sub-intervals

antoniotiman — Mon, 31 Jul 2017 12:23:21 GMT

Can You share Your input data ?

Re: split an interval in 15 min sub-intervals

Mon, 31 Jul 2017 12:41:21 GMT

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

2017-07-01 10:58:00

2017-07-01 11:03:00

BBB

AAA

Re: split an interval in 15 min sub-intervals

antoniotiman — Mon, 31 Jul 2017 14:02:58 GMT

See Attachment

Re: split an interval in 15 min sub-intervals

MarcoWedel — Tue, 01 Aug 2017 00:02:31 GMT

can you elaborate on your production numbers?

How are 45 split in 15+15+5?

thanks

Marco

Re: split an interval in 15 min sub-intervals

antoniotiman — Tue, 01 Aug 2017 04:41:35 GMT

If Production is % of Interval Time

Re: split an interval in 15 min sub-intervals

Tue, 01 Aug 2017 05:49:19 GMT

Thank you Antonio,

works like a charm!

best,

Alex

Re: split an interval in 15 min sub-intervals

Tue, 01 Aug 2017 05:50:32 GMT

Hi Marco,

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

best,

Alex

Re: split an interval in 15 min sub-intervals

MarcoWedel — Tue, 01 Aug 2017 19:12:47 GMT

15+15+5=45

Re: split an interval in 15 min sub-intervals

Wed, 02 Aug 2017 08:18:22 GMT

ah oh... 🙂

my mistake; in my rush to conceal the real data i did this mistake 🙂

Re: split an interval in 15 min sub-intervals

Wed, 02 Aug 2017 11:16:00 GMT

Now i have a new problem with the same data - Time zone plotted on a continuous chart