Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I have a really tricky problem:
One Table has State-Information (Field lState)
This state has a start time and a end time.
lState | tStart | tEnd | Duration |
---|---|---|---|
1223454 | 20121126 11:04:33 | 20121126 11:10:40 | 6 min |
1223455 | 20121126 06:00:00 | 20121127 13:00:00 | 1 d 7 h |
I would like to achive a more detailed granularity:
I have three time zones per day:
TZ1: 06:00:00 - 14:00:00
TZ2: 14:00:00 - 22:00:00
TZ3: 22:00:00 - 06:00:00 (on the next day)
The split should lead to the following output:
ID | lState | tStart | tEnd | Duration | Day | TimeZone | |
---|---|---|---|---|---|---|---|
1 | 1223454 | 20121126 11:04:33 | 20121126 11:10:40 | 6 min | 20121126 | TZ1 | |
2 | 1223455 | 20121126 06:00:00 | 20121126 14:00:00 | 8 h | 20121126 | TZ1 | |
3 | 1223455 | 20121126 14:00:00 | 20121126 22:00:00 | 8 h | 20121126 | TZ2 | |
4 | 1223455 | 20121126 22:00:00 | 20121127 06:00:00 | 8 h | 20121126 | TZ3 | |
5 | 1223455 | 20121127 06:00:00 | 20121127 13:00:00 | 7 h | 20121127 | TZ1 | |
For sure I'll need some for next loops , but as I'm quite new to QlikView this is a too big fish for me.
Maybe somebody is interested in solving this nice problem or has already solved a similar problem.
Any help is welcome
Cheers
//chesterluck
lMacStaRef | ShiftID | Start | End | Duration | tBegin | tEnd | TZ | TZStart | TZEnd |
15687141 | 1 | 02.06.2012 11:00:00 | 02.06.2012 14:00:00 | 3:00:00 | 02.06.2012 11:00:00 | 02.06.2012 21:55:18 | 1 | 02.06.2012 06:00:00 | 02.06.2012 14:00:00 |
End and TZEnd sould be 13:59:59
The second shift is missing and should be
15687141 - 2 - 02.06.2012 14:00:00 - 02.06.2012 21:55:18 - 9:55:18 - 02.06.2012 11:00:00 - 02.06.2012 21:55:18 - 2 - 02.06.2012 14:00:00 - 02.06.2012 21:59:59
Yep, it's never a good idea to compare two floating point values 😉
To make my approach stable, we need to add a small amount of time (say 1 ms) to the one end of the WHILE comparison:
...
while floor(tStart,1/3,1/4) + (iterno()-1)/3 < floor(tEnd,1/3,1/4)+interval#(1,'fff');
So the problem is that at the end of a while loop the comp might still calc and the while already goes further?? was that the problem??
I let you know whether its running smooth now
thx a lot already
Ok, it works fine..
I have a last question:
Where should I split a quantity field (Number of parts) - during the split or can i do it after.
I started to do it and im basically just counting the persentage of a Timezonepart to the complete StateTime and then i multiply it with the Quantity nr.
round((DurationStatePart/DurationState)*ShotsAll) as NumberOfShotsInTheStatePart
sth like this.
The problem iswhen the numbers are like this:
20
20,5
19,5
While rounding I would get one part more....
any idea?
>So the problem is that at the end of a while loop the comp might still calc and the while already goes further?? was that the problem??
Hm, no, I don't think so. The problem is that floating point calculations might be a bit off due to the internal representation even when the calculation is logically / mathematically correct.
So something like
floor(tStart,1/3,1/4) + 1/3 <= floor(tEnd,1/3,1/4)
might return false even (with your numbers for tStart and tEnd entered), even when it should return true, because floating point arithmetic calculations were not 100% correct, just a little off (for example, 1/3 cannot be represented correctly in the used base system).
[edit]: Even this should be more stable than my first version, though in a mathmetical sense, both should be equal:
...
while floor(tStart+ (iterno()-1)/3 ,1/3,1/4) <= floor(tEnd,1/3,1/4);
>While rounding I would get one part more.... any idea?
So your requirement is that the sum of your rounded numbers equals ShotsAll, right? So you need an unbiased tie-breaking when rounding.
I don't think there is the one and only one correct answer to this, maybe you can try something like
TEST:
LOAD recno() as ID,
TEST,
floor(TEST) as FLOOR,
TEST - floor(TEST) as DIFF
INLINE [
TEST
20
20.5
19.5
18.5
21.5
18.6
19.4
];
JOIN LOAD
round(sum(TEST)-SUM(FLOOR)) as TOSHARE
Resident TEST;
LOAD ID,
RANGESUM(FLOOR, if(recno()<=TOSHARE,1,0)) as RESULT
Resident TEST order by DIFF desc, FLOOR desc;
well I tried your solution with
while
floor(tBegin,1/3,1/4) + (iterno()-1)/3 <= floor(tEnd,1/3,1/4)
+ interval#
(1,'fff');
and it seems to be stable (I builded Sum over 50000 rows)
Did I understand it right that this solution :
while floor(tStart+ (iterno()-1)/3 ,1/3,1/4) <= floor(tEnd,1/3,1/4);
might be even more stable??
cheers
chesterluck