topic Date Based Mapping in QlikView

Date Based Mapping

niegel — Thu, 14 May 2009 05:00:42 GMT

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...

Date Based Mapping

johnw — Thu, 14 May 2009 05:05:58 GMT

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.

Date Based Mapping

niegel — Thu, 14 May 2009 05:13:28 GMT

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

Date Based Mapping

niegel — Thu, 14 May 2009 22:39:10 GMT

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

Date Based Mapping

Thu, 14 May 2009 23:10:22 GMT

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.

Date Based Mapping

niegel — Fri, 15 May 2009 02:11:57 GMT

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.