Lookup with constraint
Moderators: chulett, rschirm, roy
Lookup with constraint
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
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
You can do a lookup on ID field and use a Filter/Transformer Stage downstream to apply the conditions.
Hope that helps.
Code: Select all
Stream.StartDate > Reference.StartDate and Stream.StartDate < Referece.EndDate And Stream.EndDate > Reference.StartDate And Stream.EndDate < Reference.EndDate
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
anbu wrote: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
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
You are the creator of your destiny - Swami Vivekananda
I can not edit look up range expression.anbu wrote:Try thisCode: 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
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
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 wontkris007 wrote:Did you try doing that in a Transformer/Filter stage downstream?
and lookup stage has option to return multilple row only for one reference link. not more for more than one link
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?harryhome wrote: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 wontkris007 wrote:Did you try doing that in a Transformer/Filter stage downstream?
and lookup stage has option to return multilple row only for one reference link. not more for more than one link
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Andy,ArndW wrote: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.
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?