Interval Match "Loop" error

[Anonymous]

Hi all,

I am trying to associate our sales data with pricing info based on contract. Each order has a payer which uniquely maps to a contract, but each contract can have multiple payment terms which change over time.

Script looks like this:

Pricing:

LOAD

ContractID, StartDate, EndDate

Orders:

LOAD

OrderID, PayerID, `Date of Service`

LEFT JOIN

LOAD PayerID, ContractID

With just the above, contract lines are linked to orders in a many-to-one way, because each contract can have multiple lines covering disjoint date periods. To get this down to one-to-one, my understanding is that I should use an IntervalMatch to associate the tables like so:

Match:

IntervalMatch([Date of Service]) LOAD

StartDate, EndDate

Resident Pricing;

However, I get the following warning: "One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. Loop(s) will be cut by setting one or more tables as loosely coupled."

My data then has the attached diagram, and the association between contracts and orders remains many-to-one. Any idea how I can get a one-to-one link between Pricing and Orders tables based on both the ContractID and (Date of Service between StartDate and EndDate)?

[giovanneb · Accepted solution]

left join(Orders)

IntervalMatch([Date of Service]) LOAD

StartDate, EndDate

Resident Pricing;

[mikaelsc]

appart from the loop error, isn't there a field missing in your intervalmatch function?

a Date must match to an Interval between a Start and End Date, but shouldn't there be some kind of additional field (contractID?) in the matching process

(you match a Date of a contract to a pricing for that contract)

unless of course you apply same pricing to all contracts at the same moment in time?

afterwards, ensure correct field naming to avoid associations where you don't want them...

[Anonymous]

Thanks for this, Giovanne! I have an additional question, if you are able to answer:

If there is an additional LineID in the Pricing table, how do I join that in to the Orders table? I tried:

Left Join (Cases)

LOAD StartDate, EndDate, ContractID, LineID as PricingLineID

Resident Pricing;

But that resulted in duplication of records.