Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

datanibbler
Esteemed Contributor

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Re: Re: A complicated case for IntervalMatch

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

9 Replies
MVP
MVP

Re: A complicated case for IntervalMatch

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.



datanibbler
Esteemed Contributor

Re: A complicated case for IntervalMatch

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

MVP
MVP

Re: A complicated case for IntervalMatch

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

datanibbler
Esteemed Contributor

Re: Re: A complicated case for IntervalMatch


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.

datanibbler
Esteemed Contributor

Re: Re: A complicated case for IntervalMatch


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.


datanibbler
Esteemed Contributor

Re: Re: A complicated case for IntervalMatch

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

MVP
MVP

Re: Re: Re: A complicated case for IntervalMatch

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

datanibbler
Esteemed Contributor

Re: A complicated case for IntervalMatch

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

datanibbler
Esteemed Contributor

Re: A complicated case for IntervalMatch


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