Date Based Mapping

[niegel]

It might be a pretty basic thing for some of you but I'm trying to figure out the best approach for this.

I have 3 tables to combine (Sales Data, Territory, Sales Rep). In the example below, I am just showing the common fields that would link the tables together.

Sales Data

Inv_Date

Ship_zip

Territory

Ship_Zip

Territory Code

Sales Rep

Territory Code

Sales Rep Name

Hire Dt

Start Dt

End Dt

What I am trying to accomplish is to identify the Sales Rep for each line on the Sales Data and this will be based on the invoice date and the Start end date of the Sales Rep.

Any help? PLEASE...

[johnw]

I believe you'll want to use an intervalmatch, but I've only used it once or twice, so would need to experiment to get the right syntaxt for your example. Just check the help text for intervalmatch, and see if it's what you're looking for.

[niegel]

Thanks John... Let me look into it as I have not used that command.

[niegel]

is there any one in the forum that has an experience using intervalmatch?

[#5 · Accepted solution]

Hi,

You could do it in two steps:

1. Create a table joining Sales_Data and Territory, on the field Shi_Zip, so that the new table has following columns:

Inv_Date, Territory Code, Ship_zip

2. Now do an extended interval match to SalesRep Table on Key TerritoryCode.

I have written a small article on interval match, will post the link later. Though the Reference manual should suffice for now.

After doing the interval match, if you want to avoid the synthetic keys, do use a left join to join the tables.

[niegel]

Thanks Vidyut... It seems to work well. I will look more into the result to see if it covered what I needed. But everything makes perfect sense.