How to lookup a value based on two dimensions

[#1]

Hi,

I'd like to ask, it it is possible to lookup values from a table based on two dimension. The problem is that the team is linked to a time period. Is it possible to link the Team name from Table1 to Table2 based on the Cost center and date? Here is a simple example of the 2 tables:

Tab1:

Ccenter	Valid from Date	Valid to Date	Team
PA	01.01.2006	31.12.2006	Team1
PA	01.01.2007	31.12.2007	Team1
PA	01.01.2008	31.12.2008	Team1A
PA	01.01.2009	31.12.2009	Team1A
PA	01.01.2010	31.12.2010	Team1A
PB	01.01.2006	31.12.2006	Team2
PB	01.01.2007	31.07.2007	Team2
PB	01.08.2008	31.12.2008	Team2
PB	01.01.2009	31.12.2009	Team2A
PC	01.01.2007	31.12.2007	Team3
PC	01.01.2008	31.12.2008	Team3A
PC	01.01.2009	31.12.2009	Team3A
PD	01.01.2006	31.12.2006	Team4
PD	01.01.2007	31.12.2007	Team4
PD	01.01.2008	31.12.2008	Team4
PE	01.01.2006	31.12.2006	Team5
PE	01.01.2007	31.12.2007	Team5

Tab2:

Ccenter	Month	Year
PA	11	2006
PA	5	2007
PA	6	2007
PA	7	2007
PA	1	2010
PB	7	2006
PB	9	2008
PB	11	2008
PB	2	2009
PC	5	2007
PC	7	2007
PC	12	2009
PD	2	2007

Thx

[pover]

You can use an intervalmatch() function.

First load both tables and change Tab2 to have a date with a

Load [Valid From Date],

     [Valid To Date],

     Ccenter,

     Team

From Tab1;

Load Ccenter,

makedate(Year,Month) as Date

From Tab2;

Then do an intervalmatch

Intervalmatch(Date,Ccenter)

Load [Valid From Date],

     [Valid To Date],

     Ccenter

Resident Tab1;

Hope that works for you.

Karl

[#3]

Hi Karl,

thanks for your advice, the intervalmatch worked fine, but there is still that problem that I'm getting all combinations of the departments with the one period. As example:

PA

11

2006

for this cost center, year and period I have two lines

first with the Team1

second with the Team1A

based on the Intervals it should be only line with the Team1.

Is it possible to restrict the duplication of the lines and to show only the correct one?

Thanks

Lukas

[pover]

Strange.  Are the [Valid From Date] and [Valid To Date] fields recognized as date fields in QlikView?

Please, include that part of your script in a post to verify it.

Karl