Normal & Range Lookup

[chaks]

Description:- I have a Object,Date_From,Date_To,Area in my lookup file,Which is a slowly Changing Dimension,So I will have many records for each Object, and In My Source Dataset, I have Object,Date.

Requirement:- I need to do a lookup on Object and say we get 3 records from this Lookup, now I need to do a lookup on Date Range to get the correct Area.

Code: Select all

Look Up File
OBJECT,Date_From,Date_To, AREA
1234  ,20070101 ,20071231,  A1
1234  ,20080101 ,20081231,  A2
1234  ,20090101 ,20091231,  A3

Source File
OBJECT,  Date
1234    ,  20080201 

Desired Result 
OBJECT,Date    ,   Area
1234    ,20080201, A2 

Can I do this in one lookup stage ,If Not please suggest me.


Thanks in Advance.
Have a great day!

[ray.wurlod]

Are your dates Date data type or string? (I recall that range lookup does not work properly with dates, but that may have been fixed.)

Otherwise yours is a classic case of range lookup on reference input. Choose the lookup type as Range then double click in the Key expression field to open the Range Lookup expression editor. The rest should be intuitive.