topic Re: Sum using effective dated information in QlikView

Sum using effective dated information

Thu, 27 Sep 2012 16:50:22 GMT

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.

Re: Sum using effective dated information

swuehl — Thu, 27 Sep 2012 18:04:26 GMT

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

Re: Sum using effective dated information

Thu, 27 Sep 2012 18:58:38 GMT

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?

Re: Sum using effective dated information

Fri, 05 Oct 2012 19:47:32 GMT

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

Re: Sum using effective dated information

swuehl — Sat, 06 Oct 2012 11:17:15 GMT

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

Re: Sum using effective dated information

Mon, 08 Oct 2012 16:52:06 GMT

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.