3 Replies Latest reply: Mar 29, 2012 2:54 PM by Karl Pover

# How to lookup a value based on two dimensions

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

• ###### How to lookup a value based on two dimensions

You can use an intervalmatch() function.

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

[Valid To Date],

Ccenter,

Team

From Tab1;

makedate(Year,Month) as Date

From Tab2;

Then do an intervalmatch

Intervalmatch(Date,Ccenter)

[Valid To Date],

Ccenter

Resident Tab1;

Hope that works for you.

Karl

• ###### How to lookup a value based on two dimensions

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

• ###### How to lookup a value based on two dimensions

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