Interval Match Help

[#1]

Hi,

I have attached a sample of data where I have perfomed an interval match.  The issue I have is where I try and sum values; it appears to sum the total and then multiply by lines contained within a dropped table.

In the example 'type' A is showing 41360 when it should be 8272.  It seems to be a multiple of 5 which is the same as the number of lines of data I have.

Can anyone suggest how I can fix this?

Many thanks

[Miguel_Angel_Baeyens]

Hi Ted,

It seems that the JOINs you are performing after the IntervalMatch() are creating a higher number of rows instead of the number it should be. As a general rule, I'd suggest you to use in all cases LEFT JOIN.

Hope that helps.

Miguel

[#3]

Hi Miguel,

Thanks for your response.

I have just checked and I had an INNER join as well as a LEFT join.

I have changed both to LEFT and it doesnt seem to make any difference.

Any other ideas would be greatrly appreciated.

Many thanks

[#4]

I have attached the same app with using the left join.

Still can't get this to work.

[#5]

I added a counter and it does confirm that the data appears to be repeted 5 times (shown below).  I have tried various joins and still cannot resolve this.

Does anyone have any other technique that can be used?

Many thanks

totalcost	polstart	polend	COUNTER	type2
6850	04/12/2011	03/12/2012	21	A
6850	04/12/2011	03/12/2012	22	A
6850	04/12/2011	03/12/2012	23	A
6850	04/12/2011	03/12/2012	24	A
6850	04/12/2011	03/12/2012	25	A
0	04/12/2011	03/12/2012	16	A
0	04/12/2011	03/12/2012	17	A
0	04/12/2011	03/12/2012	18	A
0	04/12/2011	03/12/2012	19	A
0	04/12/2011	03/12/2012	20	A
0	04/12/2011	03/12/2012	1	A
0	04/12/2011	03/12/2012	2	A
0	04/12/2011	03/12/2012	3	A
0	04/12/2011	03/12/2012	4	A
0	04/12/2011	03/12/2012	5	A
1422	04/12/2011	03/12/2012	6	A
1422	04/12/2011	03/12/2012	7	A
1422	04/12/2011	03/12/2012	8	A
1422	04/12/2011	03/12/2012	9	A
1422	04/12/2011	03/12/2012	10	A
0	04/12/2011	03/12/2012	11	A
0	04/12/2011	03/12/2012	12	A
0	04/12/2011	03/12/2012	13	A
0	04/12/2011	03/12/2012	14	A
0	04/12/2011	03/12/2012	15	A

[mphekin12 · Accepted solution]

Try removing the 'Counter' field and then doing a SELECT DISTINCT from your final Cost table.

[Miguel_Angel_Baeyens]

Hi,

In the Infl.xls file where you define the ranges, you have more than one range for the same dates, and that is what is causing that issue. You will need to pass another key field to the IntervalMatch() but I don't know which one.

Hope that helps.

Miguel

[#8]

Thanks for all help; I used Load Distinct.

Many thanks