Can i use a lookup to get a key field from 2 date ranges
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 21
- Joined: Thu Jul 10, 2008 8:12 am
- Location: amherst, NY
Can i use a lookup to get a key field from 2 date ranges
Hi all,
I am trying to get a key field from a reference table based on a date range overlapping another date range in the reference table.
main table:
rec # field_id eff_dt term_dt
1 1001 2003-01-01 2007-12-31
reference table:
rec # field_id eff_dt term_dt
1 1001 2001-01-01 2002-12-31
2 1001 2004-01-01 2005-12-31
3 1001 2001-01-01 2010-12-31
4 1001 2002-01-01 2005-12-31
5 1001 2006-01-01 2010-12-31
so what i am trying to do is get is all reference table records where the main table date range overlaps the reference table date ranges.. in the example above record 1 is dropped and the lookup picks up records 2-5 because they overlap. i would even be happy with the lookup just grabbing the last record (5).
I haven't been able to figure out how to use the range lookup options correctly to do this or if it's even possible in a lookup stage.. perhaps there is another stage i should use?
what would be really nice is a join stage that allows me to add join criteria like the following:
parent.field_id=child.field_id and (parent.term_dt>=child.eff_dt and parent.eff_dt<=child.term_dt)
rather than just a straight up parent.field_id=child.field_id. is there anything like that available for datastage?
thanks for your time.
I am trying to get a key field from a reference table based on a date range overlapping another date range in the reference table.
main table:
rec # field_id eff_dt term_dt
1 1001 2003-01-01 2007-12-31
reference table:
rec # field_id eff_dt term_dt
1 1001 2001-01-01 2002-12-31
2 1001 2004-01-01 2005-12-31
3 1001 2001-01-01 2010-12-31
4 1001 2002-01-01 2005-12-31
5 1001 2006-01-01 2010-12-31
so what i am trying to do is get is all reference table records where the main table date range overlaps the reference table date ranges.. in the example above record 1 is dropped and the lookup picks up records 2-5 because they overlap. i would even be happy with the lookup just grabbing the last record (5).
I haven't been able to figure out how to use the range lookup options correctly to do this or if it's even possible in a lookup stage.. perhaps there is another stage i should use?
what would be really nice is a join stage that allows me to add join criteria like the following:
parent.field_id=child.field_id and (parent.term_dt>=child.eff_dt and parent.eff_dt<=child.term_dt)
rather than just a straight up parent.field_id=child.field_id. is there anything like that available for datastage?
thanks for your time.
After join add a transformer and use your expression
as transformer constraint.
Or use lookup stage to do range lookup
Code: Select all
parent.term_dt>=child.eff_dt and parent.eff_dt<=child.term_dt
Or use lookup stage to do range lookup
You are the creator of your destiny - Swami Vivekananda
-
- Premium Member
- Posts: 21
- Joined: Thu Jul 10, 2008 8:12 am
- Location: amherst, NY
-
- Premium Member
- Posts: 21
- Joined: Thu Jul 10, 2008 8:12 am
- Location: amherst, NY
-
- Premium Member
- Posts: 21
- Joined: Thu Jul 10, 2008 8:12 am
- Location: amherst, NY
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 21
- Joined: Thu Jul 10, 2008 8:12 am
- Location: amherst, NY