Interval Match is not working

[#1]

Hi All,

I am facing one problem here i.e actually i am having 2 tables in which my 1st table(emp) contain the following fields:

• Assignment Start Date (ASSSD Date)
• Assignment End Date (ASSED Date)
• empno
• location

And my 2nd table (PRJ) contains:

• Prjno
• empno
• amt
• ITEM Date

These 2 tables are linking through empno. Here when i select emp no which is same in this case so i am getting total amount by putting sum function directly but my requirement is that Biffurcation should be there location wise. But Location field is not there. I am trying to achieve this by implementing intervalmatch but not able to find the solution.

Basically my scenario is that Item Date is the closing date which happens at the end of each month and ASSSD & ASSED date is the assignment start and end date of different employees. For exp if there is a employee 1101 whose assignment starts from ASSSD = 1-Aug-2011 and ends in ASSED = 18-Sep--2011.

And the 2nd assignment of the same employee starts from  ASSSD = 19-sep-2011 and ends in ASSED = 31-dec-2011 but the location is different for both that's why need to see the results in different rows like one time 100 and other time 200 not the total only as 300. presently i am getting only 300 as a result biffurcation is not happening.

For your reference i am attaching the XL please have a look and let me asap.

Regards,

Saurabh Mudgil   

[swuehl · Accepted solution]

Hi,

you are saying that interval match is not working, how have you applied the intervalmatch to your data?

Maybe you want to try this:

Emp:

LOAD [ASSSD Date],

     [ASSED Date],

     empno,

     location

FROM

Test_Bifurcation.xlsx

(ooxml, embedded labels, table is Emp);

PRJ:

LOAD Prjno,

     empno,

     amt,

     [ITEM Date]

FROM

Test_Bifurcation.xlsx

(ooxml, embedded labels, table is PRJ, filters(

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4))

));

Inner Join IntervalMatch ([ITEM Date],empno) Load [ASSSD Date],[ASSED Date], empno resident Emp;

Regards,

Stefan

[#3]

Thanks Stefan ..

Your given logic is working fine. Thanks a lot for a quick reply.

Regards,

Saurabh Mudgil