

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A complicated case for IntervalMatch
Hi,
can someone help me please? I have a rather complicated scenario I mean to tackle with IntervalMatch - but it's not an easy win.
The issue is about employees and their resp. Cost_Centers. Here is the scenario:
- Any employee has a record in the masterdata table, with his join_date, his leave_date (which is standard 21001231 if the employee is still with us) and a Cost_Center - that never gets updated, it stays until the employee leaves.
- His cost_center, however, might change. In case it does, the new cost_center, with a from_date and a to_date, is in another table.
=> In case the interval(s) recorded in that other tables are over, his cost_center can be switched back.
=> To illustrate what is the difficulty about it, I use a fictitious example:
- There is an employee with the pers_nr 111100000
- He/she joined on 20120801 and is with us up to today (so he/she has a leavedate of 21001231 in the masterdata table)
- In the masterdata table, he/she has a cost_center A
- From 20130101 to 20130830, he/she was assigned cost_center B - that is one record in the other table
- WITHIN that timerange, from 20130401 to 20130620, he/she was assigned cost_center C
- From 20130831 to 21001231, he/she is assigned cost_center D
In short, the timeranges as recorded in that other table are not sequential, but may be nested in one another.
=> I'd have to somehow make this several intervals. To stick with the example:
- From 20120801 to 20121231, he/she would be assigned to cost_center A
- From 20130101 to 20130331, he/she would have cost_center B
- From 20130401 to 20130620, he/she would have cost_center C
- From 20130621 to 20130830, he/she would have cost_center B
- From 20130831 to 21001231, he/she would have cost_center D
Simple sorting of the dates won't avail me here for the intervals would be broken in that case.
I will give this some thinking and be back with any news. Any help, however, is appreciated.
Thanks a lot!
Best regards,
DataNibbler
- Tags:
- qlikview_scripting
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, it's just a blow up of your intervals. But you probably need to do something similar anyway, somepoint in your code.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Friedrich,
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';
INPUT:
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
];
TMP:
LOAD *, Date(Start+iterno()-1) as Date Resident INPUT while Start+iterno()-1 <= End order by Start asc;
drop table INPUT;
TMP2:
LOAD *,
if(Center<> peek(Center),rangesum(1,peek(Group)),peek(Group)) as Group;
LOAD Date,
LastValue(Center) as Center,
LastValue(Start) as Start
Resident TMP group by Date;
drop table TMP;
RESULT:
LOAD Group,
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi swuehl,
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.
Best regards,
DataNibbler


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi swuehl,
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi swuehl,
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
sorry, I had overlooked the last DROP TABLE.
Now that I have it, your code actually does give me exactly what I want - I only have to think my way through how it is done.
Thanks a lot!
Best regards,
DataNibbler


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, it's just a blow up of your intervals. But you probably need to do something similar anyway, somepoint in your code.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Thanks again!
Best regards,
DataNibbler


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi swuehl,
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.
Thanks again!
Best regards,
DataNibbler
