Sum using effective dated information

[#1]

Simple question, I hope, here is the scenario:

2 Tables

Table1:

Date      Qty     Person

1/2/12      2     Bob

1/8/12      3     Bob

1/20/12    4     Bob

1/22/12    5     Bob

Table2:

Person     Dept           EffectiveDate

Bob          Sales           1/1/11

Bob          Marketing   1/15/12

I need a sum formula that will sum up Qty by Dept using the person's EffectiveDate when the deparment shift took place as a parameter.  So the returned results in this example would be:

Bob     Sales            5

Bob     Marketing     9

Any help would be GREATLY appreciated.  Thank you.

[swuehl · Accepted solution]

I think you could use an INTERVALMATCH here to handle your slowly changing dimension:

Table1:

LOAD * INLINE [

Date,      Qty,     Person

1/2/12,      2,Bob

1/8/12,      3,Bob

1/20/12,    4 ,Bob

1/22/12,    5,Bob

];

Table2:

LOAD * INLINE [

Person, Dept   , EffectiveDate

Bob, Sales , 1/1/11

Bob, Marketing , 1/15/12

];

EffDates:

LOAD

EffectiveDate as EffDateStart,

if(previous(EffectiveDate) and peek(Person)=Person,Date(previous(EffectiveDate)-1),Date(makedate(2099,12,31))) as EffDateEnd

, Person, Dept Resident Table2 order by Person, EffectiveDate desc;

drop table Table2;

Inner  Join IntervalMatch(Date, Person) LOAD EffDateStart, EffDateEnd, Person Resident EffDates;

Hope this helps,

Stefan

[#3]

Thanks for the info on IntervalMatch.

Is there a way to do this within an expression or set analysis instead of loading the data differently?

[#4]

I am not sure exactly what the inner join is for.  When I include it i end up with a synthetic key, although the solution still works.  If I Exit Script before the inner join it works perfectly and does not create any synthetic keys.  Of course the IntervalMatch never runs, but why is it needed since we created a start/end date for every row in the effective date table?

Thanks again.

Richard

[swuehl]

I think I don't quite understand. Are you saying you get the same results with and and without the intervalmatch when analyzing the Qty data per Person, Dept and Date? This is not what I expect and what I do see:

If I remove the intervalmatch line I get in a chart object with dimensions Persion, Dept, Date and expression sum(Qty):

Person	Dept	Date	sum(Qty)
			14
Bob	Sales	1/2/12	2
Bob	Sales	1/8/12	3
Bob	Sales	1/20/12	4
Bob	Sales	1/22/12	5
Bob	Marketing	1/2/12	2
Bob	Marketing	1/8/12	3
Bob	Marketing	1/20/12	4
Bob	Marketing	1/22/12	5

That's due to the fact that tables are only linked by Person, so the Qty on a Date is not linked to a start / end date and Dept record.

This is what the intervalmatch does, establishing a link between the two tables using the combination of Person and Date, where Date will be linked to the appropriate interval defined by start / end date.

Person	Dept	Date	sum(Qty)
			14
Bob	Sales	1/2/12	2
Bob	Sales	1/8/12	3
Bob	Marketing	1/20/12	4
Bob	Marketing	1/22/12	5

Big difference, isn't it?

To make the intervalmatch work, you must assure that your date fields are recognized by QV as such, resulting in values with a numerical representation, so for example use an appropriate standard DateFormat at the top of your script:

SET DateFormat = 'M/D/YY';

The intervalmatch will indeed create a synthetic table and key, if needed, I think it's ok just as is, but if you want, you can get rid of the synthetic key with an additional join.

JOIN (Table1) LOAD * resident EffDates;

drop table EffDates;

And if you want to remove the start and end date, which are maybe not needed anymore:

drop fields EffDateStart, EffDateEnd;

Hope this helps,

Stefan

[#6]

OK, thank you VERY much for the time to answer this question.   The confusion I was having was because in my 'real' application (as opposed to this much simplified example), I actually have 3 tables:

1)  Name tables.

2)  Effective Dated tables with items changing like Dept, City, Title, etc., but no real data.

3)  Data Tables with the actual data and a date stamp.  (like quantiy purchased on a particular day)

So my example here was actually a bit more complicated, in a way, then the answer I needed.   For my application I will use your method of generating a start/end date so that in the expressions I can then just check the data range and sum up as needed based on the date range.  This was very helpful!!

So I really don't need the interval match....yet.  Although I bet i run into some place where I WILL need to use it and the example you provided is great.