Hello,
I am building a fact table and performing look ups on a position dimension.
I am required to do a look up on a row for the given position ID and a date. The given date is compared against the effective date in the position table. The given date is not same as the effective date, but we need to return only one row which is closest to the given date.
In the position table the primary key is position_nbr, and effdt.
In the position table, there is also a column called DIM_ID that I created which is a concatenation of position_nbr and effdt and therefore it is unique.
I have only done performing look ups for a given DIM_ID (Primary Key) in the past.
How can I perform these type of look ups using data manager or where should I start?
Thank You
Veejar
Here is a sample Data
------------------------------
POSITION_NBR EFFDT EFF_STATUS DESCR MORE COLUMNS
-------------------------------------------------------------------------------------
10359 1997/06/30 A Operator ,...
10359 1999/10/01 A Operator ,...
10359 1999/12/01 A Operator ,...
10359 2001/07/03 A Operator ,...
10359 2002/02/01 A Operator ,...
10359 2003/04/06 A Operator ,...
10359 2007/12/03 A Operator ,...
10359 2010/03/01 A Operator ,...
16296 1997/08/01 A Clerk ,...
16296 1998/02/02 A HUMAN RESOURCES ,...
16296 1998/05/02 A HUMAN RESOURCES ,...
16296 1999/05/14 A HUMAN RESOURCES ,...
16296 1999/10/01 A Generalist Assistant ,...
16296 2000/02/21 A Generalist Assistant ,...
16296 2001/07/01 A Generalist Assistant ,...
16296 2002/01/01 A Generalist Assistant ,...
16296 2002/02/01 A Generalist Assistant ,...
16296 2002/04/02 A Assistant General ,...
16296 2003/02/24 A Assistant General ,...
16296 2003/04/06 A Assistant General ,...
16296 2004/07/01 A Assistant General ,...
16296 2006/02/06 A Sr.Assistant ,...
16296 2006/12/14 A Sr.Assistant ,...
16296 2007/02/15 A Sr.Assistant ,...
16296 2007/04/23 A Sr.Assistant ,...
16296 2007/08/06 A Sr.Assistant ,...
16296 2009/04/01 A Sr.Assistant ,...
42681 2007/02/01 A Mgr, Development ,...
42681 2009/02/20 I Manager ,...
43960 2007/10/26 A Supervisor ,...
-- This query return one row and from this row I can use the other columns as my look up values.
SELECT *
FROM PS_POS POSITION
WHERE (POSITION.POSITION_NBR, POSITION.EFFDT) IN (
-- SELECT THE CLOSEST EFFECTIVE DATE ROUNDED TO THE MINIMUM from the position table.
SELECT POSITION_NBR, MIN (DATERANGE.EFFDT)
FROM PS_POS DATERANGE
WHERE POSITION_NBR = 16296
AND EFFDT >= '2005-11-20'
GROUP BY POSITION_NBR);
Have a read of the "Process Late Arriving Facts" section of the Data Manager documentation, specifically the "Use closest reference member" option, it should do what you want.