range lookup

[sigma]

I have a problem using range option in the Lookup stage.

I need to perform Lookup for the combination of 2 fields:

Field1 Comp_Code - one to one lookup
Field2 ACCT - range lookup (=>ACCT_from, =<ACCT_to)

The stage does not let me to include ACCT_from and ACCT_to to the key.

Here is the situation that creates a problem - same comp code for two different ranges:

Code: Select all

ACCT_from   ACCT_to   Comp_Code
000000	    299999	    1850
400000	    999999	    1850

When I run the process it picks up only first occurrence of 1850 and ignores the second, third ...

Please advise.

[ray.wurlod]

Looks like you've tried to create a range lookup on the stream input. Try creating the range lookup on the reference input.

[ray.wurlod]

Do you have the "return multiple rows from reference link" option selected?

[chulett]

However, that's not an example of "multiple ranges" as your input data only qualifies for one range in your example. It's not about how many you have to check.

[chulett]

The stage does not let me to include ACCT_from and ACCT_to to the key.

Help us with what that means. You should certainly be able to bind your one input column to both of the lookup columns... what is in your 'range expression'?

[sigma]

Do you have the "return multiple rows from reference link" option selected?

Option "return multiple rows" helped! Thank you very much!!! :D

[chulett]

Confused as to how that actually fixed the problem as there was no need to return multiple anything in the example given, just one.

[djbarham]

Confused as to how that actually fixed the problem as there was no need to return multiple anything in the example given, just one.

Thanks guys, this thread helped me too.

Craig, I agree with your confusion. I have the same situation where I have 2 columns with an equality match on the lookup, the one column on the input stream that matches on a range in the lookup link.

One clue was the following error in the job log:

Code: Select all

lu_match_current_records,0: Ignoring duplicate entry; no further warnings will be issued for this table

While the ranges mean that we don't have multiple rows matched for any input, the definition of the lookup link only has the equality matched fields tagged as key fields. So, in terms of the fields identified as key fields, yes, there are multiple matches, but the range sorts it out.

I'm inclined to call it a bug (or at least a trap for young players).

Thanks to Ray for the answer (again).