Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think you need to have it like this:
IF(Interval_Min<'=07:30','07:30',
if(Interval_Min<'=10:00','10:00', etc
Hi Joonas,
I'll try. But why? What does that '=' change about the operator?
Thanks a lot!
Best regards,
DataNibbler
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',
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!
Oh sorry, typoed
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.
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.
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
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