topic Re: A complicated case for IntervalMatch in QlikView

A complicated case for IntervalMatch

datanibbler — Wed, 06 Nov 2013 12:22:52 GMT

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

Re: A complicated case for IntervalMatch

swuehl — Wed, 06 Nov 2013 13:20:08 GMT

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.

Re: A complicated case for IntervalMatch

datanibbler — Wed, 06 Nov 2013 13:37:56 GMT

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

Re: A complicated case for IntervalMatch

swuehl — Wed, 06 Nov 2013 13:44:23 GMT

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:

QlikView Date fields

Re: Re: A complicated case for IntervalMatch

datanibbler — Wed, 06 Nov 2013 13:50:37 GMT

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.

Re: Re: A complicated case for IntervalMatch

datanibbler — Wed, 06 Nov 2013 14:06:42 GMT

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.

Re: Re: A complicated case for IntervalMatch

datanibbler — Wed, 06 Nov 2013 14:11:40 GMT

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

Re: Re: Re: A complicated case for IntervalMatch

swuehl — Wed, 06 Nov 2013 14:19:03 GMT

Yes, it's just a blow up of your intervals. But you probably need to do something similar anyway, somepoint in your code.

Re: A complicated case for IntervalMatch

datanibbler — Wed, 06 Nov 2013 14:28:44 GMT

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

Re: A complicated case for IntervalMatch

datanibbler — Wed, 06 Nov 2013 16:20:44 GMT

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