it would make things a little easier if you could attach some sample input data in an appropriate format or even a small sample app.
In principle, I think you can try something like
Set DateFormat ='YYYYMMDD';
LOAD date#(Start) as Start, date#(End) as End, Center INLINE [
Start, End, Center
20120801, 20121231, A
20130101, 20130830, B
20130401, 20130620, C
20130831, 20131231, D
LOAD *, Date(Start+iterno()-1) as Date Resident INPUT while Start+iterno()-1 <= End order by Start asc;
drop table INPUT;
if(Center<> peek(Center),rangesum(1,peek(Group)),peek(Group)) as Group;
LastValue(Center) as Center,
LastValue(Start) as Start
Resident TMP group by Date;
drop table TMP;
date(min(Date)) as Start,
date(max(Date)) as End,
only(Center) as Center
Resident TMP2 Group by Group;
drop table TMP2;
You can probably already use TMP2 table for what you finally need, this is already a flat table with the Center allocation for each Date.
I'm a bit confused with your example: What is the field >Group<?
You use that in the second LOAD, but I don't see where it comes from?
When you can tell me that, I can tell you if you're close to what I need to do.
In the meantime, I will put together a test_app with some sample data.
Also, I don't quite understand the date# fct. - the integrated help_file is somehow confusing...
I'll be back with my sample app soon.
You should be able to just copy the code into your editor and let it load, this should create a final table which I think should match what you expect.
Group is created with this load expression:
if(Center<> peek(Center),rangesum(1,peek(Group)),peek(Group)) as Group
It just numbers the consecutive sequence of dates one is belonging to the same Center.
I think the date#() function should be explained in the Help or ref manual, and also here:
I think I kind of understand. I have to give this some more thought.
If I understand this point correctly, however, your table TMP is just a "blowup" of what you get from INPUT.
=> If the last interval in that table has an end_date of 21001231, that would create a lot of unneeded data, but that could be helped by replacing that with today's date.
Here is the sample app I put together, exactly like it is in this example - with a little twist: The last interval in that table (INPUT) has an end_date of 21001231 - but as you see in the masterdata table, that employee left on 20130831.
HR_CC_QComm.qvw 155.2 K
I tested your code and it is quite close to what I need - but not altogether there. Also, I don't understand why it does what it does and that's not acceptable - I'll have to work on this ;-)
However - your code assigns the employee two cost_centers between 20130401 and 20130620. There can be only one, however and there's no exact telling (from the numerical value or a letter) which one is correct.
Yes, it's just a blow up of your intervals. But you probably need to do something similar anyway, somepoint in your code.
HR_CC_QComm_SW.qvw 147.8 K
Thanks a lot!
Your code works and does what I want, the greatest part of it anyway. It's only for one employee, but well, it can be looped over for everyone, it will just take a while. No problem on the technical side.
Now it's up to me, I will think my way through it and build on it to get exactly what we need.
now I've found out just what your code is doing. You were right, I don't need that last table RESULT, I can stop when I have TMP2.
Accordingly, I also deleted the first of that double-set of LOAD statements and I understand the LastValue() concept now.
Now I just have to insert the SQL SELECTs to get the info from our database and then I have to find a way of looping over every employee and doing all this for every one - though some might not have those interim_cost_centers at all, so maybe I can skip that operation for those and just assume the default_cost_center for every day for them.
When that works, we'll be a big step further along the way to getting our calculations straight.