Discussion Board for collaboration on QlikView Scripting.
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.
Time($(vShiftStart) + Time#('00:01:00')*(IterNo()-1), 'hh:mm') as Interval_Min
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:
if(Interval_Min<'22:30','22:30','22:30')))))))) as Interval_2H,
Quite easy it seems, though maybe not the most elegant way of doing this. For some reason, however, the 2h-intervals
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
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!
For example, if you have time "14:00". Here it checks if it smaller than 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',
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.
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.
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.
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.