Lookup Range With Dates

algfr · Post by **algfr** » Thu Jul 23, 2009 2:20 pm

Hello guys,

I have trouble using the range lookup feature from the lookup stage. I've seen an example on the web but it is not what I would expect.

My problem is easy

1) I have a main flow which contains a calendar_date (Date Format) out of a row generator (i create a calendar table).

2) I have a lookup flow which holds a start_date and end_date out of Oracle.

So I want to check that the calendar_date is between the start_date and the end_date.

It seems no to be working as no data comes out (I want to drop bad records). I've set up the conditions already.

My question are :

1) In the example I've seen on the web, it's the opposite, the start date and end date are on the main link and the single date on the reference link. Is it the way it is supposed to be ?

2) What are the range checkboxes for ? It seems not to be required to specifiy the lookup ?

3) The Range() function that appears is supposed to be on the referenced field or the main field ?

4) Is it okay to lookup between a Timestamp and a Date ? I had to use a Timestamp because my ref link comes out of Oracle.

If you know where to find a good example, I'm glad to take it.

Thank you a lot and good day

algfr · Post by **algfr** » Thu Jul 23, 2009 2:25 pm

This is the example I'm mentionning

This is the opposite of what I would like to do (start date and end date are on the main link, I want them on the ref link)

algfr · Post by **algfr** » Thu Jul 23, 2009 2:37 pm

http://it.toolbox.com/blogs/infosphere/ ... kups-16911

Kryt0n · Post by **Kryt0n** » Thu Jul 23, 2009 5:17 pm

The range can work either way, two things to check (that spring to mind)

1) Have you got your >=/<= the right way round?
2) Are you getting warning messages in your log regarding duplicates? If so, you may find you are losing the range that you want, particularly an issue if you are working off another reference key field

algfr · Post by **algfr** » Thu Jul 23, 2009 5:19 pm

Kryt0n wrote:The range can work either way, two things to check (that spring to mind)

1) Have you got your >=/<= the right way round?
2) Are you getting warning messages in your log regarding duplicates? If so, you may find you are losing the range that you want, particularly an issue if you are working off another reference key field

Thank you

1) Yes

2) No

Can I have issues with Timestamp against date ? Also Do i Have to check the checkbox (range) ?

Kryt0n · Post by **Kryt0n** » Thu Jul 23, 2009 10:06 pm

The range box on the main stream needs to be checked. If you have specified the >= and <= already then the range box should already be checked.

Timestamp verse date I'm not sure but converting one to the other is easy enough so give it a try.

algfr · Post by **algfr** » Fri Jul 24, 2009 3:11 am

I needed to convert to date using a modify stage first

Also had forgotten to trigger the 'a..z' range condition.

It works great, thanks a lot.