Lookup with constraint

[harryhome]

Hi Need to look up a value with constraint(as in stream records start date and end date should be between reference records start date and end date)

In look up stage, doesn't seems to do the comparision in my case, is there any other way to do look up with constraint

Main stream data has ID, start_date and end_date
and reference stream data has ID, start_date and end_date

Main steam will look up on ID where start_date and end_date is between reference data start_date and end_date

[anbu]

Use range lookup for start_date and end_date

[kris007]

You can do a lookup on ID field and use a Filter/Transformer Stage downstream to apply the conditions.

Code: Select all

Stream.StartDate > Reference.StartDate and Stream.StartDate < Referece.EndDate And Stream.EndDate > Reference.StartDate And Stream.EndDate < Reference.EndDate

Hope that helps.

[harryhome]

Use range lookup for start_date and end_date

Tried using same getting error in compilation as

##E IIS-DSEE-TFKL-00079 15:08:02(009) <transform> Error when checking composite operator: Error parsing range option. Comparison operators can't be of the same type "Main.EFF_DT >= reference.EFF_DT AND Main.EXP_DT >= reference.EFF_DT"
##E IIS-DSEE-TFKL-00079 15:08:02(010) <transform> Error when checking composite operator: Error parsing range option. Comparison operators can't be of the same type "Main.EFF_DT <= reference.EXP_DT AND Main.EXP_DT <= reference.EXP_DT"

EFF_DT is Start date
EXP_date is End date

[anbu]

Try this

Code: Select all

Main.EFF_DT >= reference.EFF_DT AND Main.EFF_DT <= reference.EXP_DT
Main.EXP_DT >= reference.EFF_DT AND Main.EXP_DT <= reference.EXP_DT

[harryhome]

Try this

Code: Select all

Main.EFF_DT >= reference.EFF_DT AND Main.EFF_DT <= reference.EXP_DT
Main.EXP_DT >= reference.EFF_DT AND Main.EXP_DT <= reference.EXP_DT

I can not edit look up range expression.
Main.EFF_DT >= reference.EFF_DT AND Main.EXP_DT >= reference.EFF_DT
This means I am comparing eff_dt and exp_dt from main to eff_dt of reference in lookup stage

[kris007]

Did you try doing that in a Transformer/Filter stage downstream?

[harryhome]

Did you try doing that in a Transformer/Filter stage downstream?

yes , so wen lookup on ID only , multiple entries are present in reference table for a ID then only one entry gets picked up and other wont

and lookup stage has option to return multilple row only for one reference link. not more for more than one link

[ray.wurlod]

EACH lookup stage has option to return multilple row only for one reference link.

[kris007]

lookup stage has option to return multilple row only for one reference link. not more for more than one link

So, do you have more than one reference link (which you did not mention in your first post)?

[harryhome]

yes i have more than one reference link, i am planning to use one lookup stage for one reference link and then carry forward result to 2nd lookup with 2nd reference link

[agpt]

Did you try doing that in a Transformer/Filter stage downstream?

yes , so wen lookup on ID only , multiple entries are present in reference table for a ID then only one entry gets picked up and other wont

and lookup stage has option to return multilple row only for one reference link. not more for more than one link

Could you please tell me which option exactly let us return multiple rows if input has one row but reference data has multiple rows on key column?

[ArndW]

In the lookup stage, double-click on the Condition or right-mouse-click on the left pane and choose "conditions" and you will see a drop down box on the top left of the window that allows you to choose the link from which multiple rows can come.

[agpt]

In the lookup stage, double-click on the Condition or right-mouse-click on the left pane and choose "conditions" and you will see a drop down box on the top left of the window that allows you to choose the link from which multiple rows can come.

Andy,

Let me give the exact example what I want:

I have input as :

ID
1
2
3
4

Reference data to be looked upon as:
ID vara
1 A
2 B
2 B
3 C
4 D
4 D

The normal output from lookup wuld have:

ID vara
1 A
2 B
3 C
4 D

but the output I want:

ID vara
1 A
2 B
2 B
3 C
4 D
4 D

i.e. multiple records if reference has multiple records. Would this "conditions" would handle it?

[ArndW]

Yes.