After much searching here and trying many ideas, I have decided to post my question here, in the hopes somebody can help.
I have two tables, A & B say, which have to join on a field which is not a key field in either table. The entries in Table A for this field are in a one to many relationship with entries in table B. The business problem requires that I find for each unique entry in table A, a corresponding entry in Table B, which is possible when the other fields in table are considered.
More specifically: Table A contains Claims and table B contains features of the policy which are date dependant: Only 1 feature is valid per claim.
I thought of using interval match, but haven't been successful.
My next thought is, and where I need help with the syntax is :
Load table A, then inner join with table B, with a WHERE clause that depends on the values of table A.
here is a simplified example:
TableA:
LOAD * INLINE [
ClaimNo, Discount, ClaimDate
100000, A, 1/9/2010
100001, B, 1/9/2010
100002, B, 2/9/2010
100003, B, 3/9/2010
100004, A, 2/9/2010
100005, B, 2/9/2010
100006, A, 3/9/2010
100007, B, 1/9/2010
100008, A, 2/9/2010
100009, B, 3/9/2010
];
TableB:
LOAD * INLINE [
DiscountFeature, Discount, Start, End
100, A, 1/9/2010, 2/9/2010
100, B, 3/9/2010, 6/9/2010
101, C, 2/9/2010, 3/9/2010
101, F, 1/9/2010, 1/9/2010
102, D, 1/9/2010, 3/9/2010
103, A, 3/9/2010, 6/9/2010
103, B, 1/9/2010, 2/9/2010
104, E, 1/9/2010, 6/9/2010
105, C, 1/9/2010, 1/9/2010
105, G, 2/9/2010, 3/9/2010
106, F, 2/9/2010, 3/9/2010
106, G, 1/9/2010, 1/9/2010
];
In the end, I would like a table in the data model, with ClaimNo and DiscountFeature. Right now the resulting table with the inner join contains 2 records for each claim, one in the valid period and one not. I can do most expressions by using the claim date to filter right answer, but I think that is too expensive. I also don't need twice the records I get from the join.
So, obviously this is simplified, I would be happy to answer any questions for ideas to try.
