Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

datanibbler
Not applicable

Still reworking a code


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

Tags (1)
9 Replies
joonasjaspi
Not applicable

Re: Still reworking a code

I think you need to have it like this:

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

datanibbler
Not applicable

Re: Still reworking a code

Hi Joonas,

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

Thanks a lot!

Best regards,

DataNibbler

joonasjaspi
Not applicable

Re: Re: Still reworking a code

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',

datanibbler
Not applicable

Re: Still reworking a code


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!

joonasjaspi
Not applicable

Re: Re: Still reworking a code

Oh sorry, typoed

datanibbler
Not applicable

Re: Still reworking a code


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.

bwisenosimenkg
Not applicable

Re: Still reworking a code

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.

datanibbler
Not applicable

Re: Still reworking a code

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

marcus_sommer
Not applicable

Re: Still reworking a code

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