topic Re: Re: Still reworking a code in QlikView

Still reworking a code

datanibbler — Thu, 31 Jul 2014 07:26:52 GMT

Hi,

in an old code, I have introduced, with some help from the Community, a new code to generate minute_intervals between 5:20 am and 10:30 pm. Here is the code.

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

Time($(vShiftStart) + Time#('00:01:00')*(IterNo()-1), 'hh:mm') as Interval_Min
AutoGenerate (1)
While Time($(vShiftStart) + Time#('00:01:00')*(IterNo()-1), 'hh:mm') <= $(vShiftEnd);
;

Later, in a consecutive piece of code, I have the following IF-construct:

LOAD

...

IF(Interval_Min<'07:30','07:30',
if(Interval_Min<'10:00','10:00',
   if(Interval_Min<'12:00','12:00',
   if(Interval_Min<'14:00','14:00',
   if(Interval_Min<'16:00','16:00',
   if(Interval_Min<'18:30','18:30',
   if(Interval_Min<'20:30','20:30',
   if(Interval_Min<'22:30','22:30','22:30')))))))) as Interval_2H,

Interval_Min

RESIDENT Plan_Kolli_Temp

Quite easy it seems, though maybe not the most elegant way of doing this. For some reason, however, the 2h-intervals

- 07:30

- 10:00

- 12:00

INCLUDE that exact time (the minute-interval 07:30 is included in the 2h-interval 07:30 and the like for the others)

<=> The later intervals

- 14:00

- 16:00

- 18:30

- 20:30

- 22:30

do NOT include that exact time (the 2h-interval 14:00 includes the minute-intervals up to 13:59 and the like)

=> Can anybody tell me why this is so?

Thanks a lot!

Best regards,

DataNibbler

Re: Still reworking a code

Anonymous — Thu, 31 Jul 2014 07:32:29 GMT

I think you need to have it like this:

IF(Interval_Min<'=07:30','07:30',
if(Interval_Min<'=10:00','10:00', etc

Re: Still reworking a code

datanibbler — Thu, 31 Jul 2014 07:41:58 GMT

Hi Joonas,

I'll try. But why? What does that '=' change about the operator?

Thanks a lot!

Best regards,

DataNibbler

Re: Re: Still reworking a code

Anonymous — Thu, 31 Jul 2014 08:09:58 GMT

For example, if you have time "14:00". Here it checks if it smaller than 14:00

if(Interval_Min<'14:00','14:00',

It isn't, 13:59 is the last value that makes this statement true. So it tests with next if-sentence, is it smaller than 16:00? (Yes) But in your case you want to have value 14:00 in "14:00" category. So we add condition that it needs to smaller OR excatly the same value, by adding "=". "<=" could be also written as

if(Interval_Min<'14:00' OR Interval_Min='14:00','14:00',

Re: Still reworking a code

datanibbler — Thu, 31 Jul 2014 08:24:34 GMT

Ah, I understand.

I was just confused because you had enclosed that = into the quotes in your post. Of course, the operator <= is known to me 😉

Thanks a lot!

Re: Re: Still reworking a code

Anonymous — Thu, 31 Jul 2014 08:37:57 GMT

Oh sorry, typoed

Re: Still reworking a code

datanibbler — Thu, 31 Jul 2014 08:51:41 GMT

No,

for some reason it still doesn't work as expected: I have replaced all the '<' operators with '<=', but now the 2H-intervals '10:00' and '12:00' are wrong:

I calculate the hr simply with >> Hour(Interval_Min) <<

Strangely, some of the intervals are correct, others are not though I am now consistently using the '<=' operator throughout that IF-construct:

- The 2hr-interval '07:30' includes that exact time, the hr_intervals are correct: 5, 6, 7

- The 2hr-interval '10:00' includes that exact time, the hr_intervals are incorrect: 7, 8, 9, 10 - I don't want 10 in there

- '12:00' includes that exact time, hr_intervals are incorrect: 10, 11, 12 - I don't want 12 in there

- '14:00' does NOT include that exact time, hr-intervals are correct - 12, 13

- '16:00' does NOT include that exact time, hr-intervals are correct - 14, 15

- '18:30', '20:30' do include that exact time, that is correct as we have a half-hour here.

Re: Still reworking a code

simenkg — Thu, 31 Jul 2014 09:32:58 GMT

And you are sure the exact time 16:00 exists? And that it is not 16:00:00.00001?

try changing Hour(Interval_Min) to Floor(Hour(Interval_Min)) and see if it makes any difference?
<= will give you the exact time 16 while < will not.

Re: Still reworking a code

datanibbler — Thu, 31 Jul 2014 09:39:20 GMT

Hi all,

thanks for the new ideas!

I have some other things to attend to right now - after all, it works, I was just trying to make it more elegant by introducing this code with the minute-intervals - and if I do that, I have to smoothen it out so that it works again. Until I have that, I leave the "live" code as it is.

I will get back to this tomorrow - I really want the "fundaments" of our QlikView_architecture as smooth and trim as they can be.

Best regards,

DataNibbler

Re: Still reworking a code

marcus_sommer — Thu, 31 Jul 2014 11:11:27 GMT

Hi DataNibbler,

two other methods could be helpful especially if the condition-check is much larger.

First you could use class() to create cluster - with none homegenous cluster-size is it more complicated and you will need a few if-loops inside and/or outside.

Second you could use mapping to a master-timetable or to a mapping-table. Difficult at this is to round your value (Interval_Min) to exactly those values which you have in mapping.

- Marcus