topic Re: Please tell me what this code does in QlikView

Please tell me what this code does

datanibbler — Wed, 23 Jul 2014 08:51:39 GMT

Hi,

I have a code here that a colleague of mine developed with the help of a consultant a year or more ago. I assume that it works fine, we never had any problems relating to it.

Unfortunately, it is not commented or documented at all and now I want to build an app based on this, which proves a bit difficult without understanding the basis ...

The code clearly autogenerates some intervals. The question is, which? An hour has 60min, but 100 "industry minutes".

I guess it is "real" minutes - 60 to an hour - that the code generates, but I'm not sure, I can't read it.

LOAD if(right(520+IterNo()-1,2)<60,520+IterNo()-1) as Interval_Min
//if(right(IterNo()-1,1)=0,530+IterNo()-1) as Interval_10_Min
AutoGenerate (1)
While 520+IterNo()-1 <= 2230;

Thanks a lot!

Best regards,

DataNibbler

Re: Please tell me what this code does

alexandros17 — Wed, 23 Jul 2014 09:02:31 GMT

IterNo works as an index that for each loop increase of 1 unit so it is simply a counter;

autogenerate insert a row (becaure there is 1 as parameter) in each loop

so you are loading (while 520+ the number of times the loop is executed <= 2230 - you are creating 2230-520 rows!)

each row has a field Interval_Min where if 520+counter (e.g. 1232 -> 32 the right 2 char) < 60 you have 1231

Re: Please tell me what this code does

datanibbler — Wed, 23 Jul 2014 09:07:44 GMT

Thanks!

So - Interval_Min is to mean "interval minute" - so I have 1.231 minutes?

That takes me one step further. Let's see if I can reconstruct it.

Re: Please tell me what this code does

SunilChauhan — Wed, 23 Jul 2014 09:08:11 GMT

it will genrate no from 520 to 2230 simply as in Interval_min field

Re: Please tell me what this code does

jonathandienst — Wed, 23 Jul 2014 09:15:20 GMT

Looks like it creates 100 sequence number entries per hour from 08:40 to 22:30, but all the ((value modulo 100) > 60 are null). Values less than 60 contain the sequence number.

HTH

Jonathan

Re: Please tell me what this code does

datanibbler — Wed, 23 Jul 2014 09:22:00 GMT

Hi Jonathan,

okay so it does return "real" minutes, 60 to an hour. That clarifies a bit - but why 08:40? It's 05:20, isn't it - that would be the beginning_time of our early shift, 22:30 is the end of our late_shift. So this code probably generates the intervals in "real" minutes of one working day.

Re: Please tell me what this code does

martynlloyd — Wed, 23 Jul 2014 10:32:23 GMT

Hi,

that explains why it uses Right, 2.

It is a poor way of doing it, as 40% of the processing is wasted, would have been better to use a proper time value.

Re: Please tell me what this code does

datanibbler — Wed, 23 Jul 2014 11:17:52 GMT

Hi Martin,

can you think of a way to do the same thing better, more efficiently that is? This code was written by a colleague with an external consultant a long time ago, so it's well possible that even the consultant, at that point, did not know as much as he does now ...

Every tweak that serves to reduce processing time and resource usage, by however little, is helpful since we have plans of having a lot more QlikView around in the future...

Thanks a lot!

Best regards,

DataNibbler

Re: Please tell me what this code does

martynlloyd — Wed, 23 Jul 2014 13:32:52 GMT

DN, This will generate the intervals as Dual values (Time/Number), meaning you can use them in time functions, and they sort correctly.

If you want other intervals, e.g. every 5 mins, you can edit the Time#('00:01:00')

Set vShiftStart=Time#('5:20:00');
Set vShiftEnd=Time#('22:30:00');

LOAD
Time($(vShiftStart) + Time#('00:01:00')*(IterNo()-1)) as Interval
AutoGenerate (1)
While Time($(vShiftStart) + Time#('00:01:00')*(IterNo()-1)) <= $(vShiftEnd);

Regards,

Marty.