Help in Range lookup

[vskr72]

I have a ID1 and date col coming from a stream input called disp_dt. Now there is a reference table where we have the following columns

ID2
t_start_dt
t_end_dt
s_start_dt
s_end_dt

I am getting both these inputs to a lookup and I want to perform the following:

1. ID1=ID2
2. disp_dt >= t_start_dt and disp_dt <= t_end_dt and disp_dt>=s_start_dt and disp_dt <= s_end_dt

How to achieve this using range lookup. Thanks,

Kumar

[vskr72]

This is how my job looks like:
[code]
Ora_Ent_Stage_main --> Lookup_stage--> FLat_File
:
:
Ora_Ent_Stage(LKP)
[/code]

1. Disp_DT is coming from the Ora_ent_stage_main
2. Other dates - t_start_dt, t_end_dt, s_start_dt and s_end_dt comes from the reference table - Ora_Ent_Stage(LKP)

I could only do the following:
1. ID1=ID2 ( in the ref input)
2. disp_dt >= t_start_dt and disp_dt <= t_end_dt (in the lookup)

I am not able to include the 3rd condition:

disp_dt>=s_start_dt and disp_dt <= s_end_dt

I used to work in Informatica and its pretty straight forward to implement this. Any tips pls.[quote][code][/code][/quote]

[ray.wurlod]

Your question embodies the solution.

On the reference input link in the Lookup stage choose Range as the lookup type then double click on the value cell to open the range specification editor.

Can you not simply continue to edit the expression in the range lookup expression editor? Add another AND and keep going?

[*Topic relocated per request - Andy*]

[vskr72]

Ray - Can you pls tell me how do we put another AND condition. I did not have an option to add another condition there. I think I am missing something here. Can you pls explain? Thanks.

Your question embodies the solution.

On the reference input link in the Lookup stage choose Range as the lookup type then double click on the value cell to open the range specification editor.

Can you not simply continue to edit the expression in the range lookup expression editor? Add another AND and keep going?

[*Topic relocated per request - Andy*]

[ray.wurlod]

I don't have access to a DataStage that I can use to test at the moment, but believe that you can add more AND conditions in the range lookup expression editor grid. Will have to put this on hold till I can test - maybe next week.

[mk_ds09]

Hi

Do have a look at the following link which will help you to get into more datails about the range lookup..It is also having some examples attached with this which can help you sort out your issues..

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

[vskr72]

I have a stream inout and 1 reference input with a lookup. I have the 3 conditions
- 2 Range lookups in the streams key expression
- 1 Equality lookup in the reference

When I am doing this the output is messy. If i use either of the range lookups or the equality lookup, the output is as expected. But, if I use both, then the output is wrong. Is it not possible to use both? Thanks.

[ray.wurlod]

What if you use two Lookup stages, so that you're not mixing lookup types? A range lookup compiles to a transform operator whereas a normal lookup compiles to a composite (LUT_CreateOp/LUT_ProcessOp) operator.