topic Re: What is wrong with my loop? in QlikView

What is wrong with my loop?

Mon, 14 Apr 2014 11:44:37 GMT

Hi all,

I have a loop that I created and works well but I get a few of the records that don't work out well.

This is my code:

((Time(Time([Arrival Time]-1/1440 + IterNo()/1440)))-5/1440) AS [Time],

[TempSchedules5] WHILE IterNo()/1440 < (([Departure Time]+6/1440) - [Arrival Time]);

DROP TABLE [TempSchedules5];

So for example, If i have an arrival time of 8:30 and a departure time of 9:00. I want it to loop and create a record for each minute beginning from 5 minutes before and display the following minutes:

Time:

08:25

08:26

08:27

08:28

08:29

08:30

08:31

08:32

08:33

08:34

08:35

08:36

08:37

08:38

08:39

08:40

08:41

08:42

08:43

08:44

08:45

08:46

08:47

08:48

08:49

08:50

08:51

08:52

08:53

08:54

08:55

08:56

08:57

08:58

08:59

09:00

The problem is that it works perfectly for most records, but i noticed that for some, it will only create the records until minute 8:59 in that example.

Does anyone know why this happens?

Many thanks in advance,

Kind Regards,

Bruno Pereira

Re: What is wrong with my loop?

Mon, 14 Apr 2014 12:33:59 GMT

Hi,

Try this simple script to generate the time field

Let vA =Time(Time('8:30','hh:mm')-Time('00:05','hh:mm'),'hh:mm');
Let vB =Time(Time#('9:00','hh:mm'),'hh:mm');
Let vC =Time('$(vB)'-'$(vA)','mm');

For i= 0 to $(vC)

TimeTable:
Load
Time('$(vA)'+'00:0$(i)','hh:mm') as Time
AutoGenerate 1;
Next;

Note:

i=0 ---> If you want the records from 8:25 to 9:00

i=1 ---> If you want the records from 8:26 to 9:00

Re: What is wrong with my loop?

Mon, 14 Apr 2014 12:51:14 GMT

Hi,

Each record will have different times, they won't all be from 8:30-9:00.. They will have all sorts of times through out the day.

Will that still work if there are different times? without having to make a variable for each one?

Thanks,

Re: What is wrong with my loop?

Tue, 15 Apr 2014 04:04:59 GMT

If i am correct,

you need to create a loop for time field from Arrival time to Departure time with increment of 1 minute.

The samething is there in my earlier post

there vA = your arrival time - 5 minutes

vB = your departure time

Each record will have different times ---> What it means?

Re: What is wrong with my loop?

Tue, 15 Apr 2014 08:57:40 GMT

Yes you are correct Ravi, it needs to loop for all the minutes between Arrival time and Departure time.

But I can't put:

'8:30' in Let vA =Time(Time('8:30','hh:mm')-Time('00:05','hh:mm'),'hh:mm'); because some records will be from 8:30-9:00, and others may be from 10:10-11:30 or 12:35-16:20 for example. Does that answer your question?