Lookup help or Join
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 17
- Joined: Mon Feb 18, 2008 7:56 pm
- Location: Warwick, RI
Lookup help or Join
I have a source that has a Max Hist Date H only in on link. The other is a Fact table = F.
How do I test if the F.START_DATE > H.MAX_HIST_DATE
I'm sorry it prob is easy but I can't get it.
How do I test if the F.START_DATE > H.MAX_HIST_DATE
I'm sorry it prob is easy but I can't get it.
jOE fOnSEca
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard.
Load the max date into a Lookup File Set with Entire partitioning. This has a constant (say "X") as its key, and the max date as a non-key field.
Use a Lookup stage to lookup the record whose key is "X" from the Lookup File Set. You need a Column Generator stage upstream of the Lookup stage to generate the "X" (a Cycle with only one Value).
Load the max date into a Lookup File Set with Entire partitioning. This has a constant (say "X") as its key, and the max date as a non-key field.
Use a Lookup stage to lookup the record whose key is "X" from the Lookup File Set. You need a Column Generator stage upstream of the Lookup stage to generate the "X" (a Cycle with only one Value).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 17
- Joined: Mon Feb 18, 2008 7:56 pm
- Location: Warwick, RI
Premium Content
ray.wurlod wrote:Welcome aboard.
Load the max date into a Lookup File Set with Entire partitioning. This has a constant (say "X") as its key, and the max date as a non-key field.
Use a Lookup stage to lookup the re ...
I just signed up for Premium Content how do i view the response?
jOE fOnSEca
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Re: Premium Content
once you get your premium account activatedjoefonseca79 wrote:ray.wurlod wrote:Welcome aboard.
Load the max date into a Lookup File Set with Entire partitioning. This has a constant (say "X") as its key, and the max date as a non-key field.
Use a Lookup stage to lookup the re ...
I just signed up for Premium Content how do i view the response?
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 17
- Joined: Mon Feb 18, 2008 7:56 pm
- Location: Warwick, RI
Re: Premium Content
it still doesn't seem to be working. Is there an issue with Lookup stage for v8?priyadarshikunal wrote:once you get your premium account activatedjoefonseca79 wrote:ray.wurlod wrote:Welcome aboard.
Load the max date into a Lookup File Set with Entire partitioning. This has a constant (say "X") as its key, and the max date as a non-key field.
Use a Lookup stage to lookup the re ...
I just signed up for Premium Content how do i view the response?
jOE fOnSEca
Please describe your job design and what output you are getting. Ray's stated solution is quite simple. You're going to generate a single row two column (key 'X' and your max value) lookup dataset that is Entire partitioned (all partitions will be broadcast a complete copy of the lookup set - 1 row). Your output should match the lookup on a your key column literal value and then constrain the output.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 17
- Joined: Mon Feb 18, 2008 7:56 pm
- Location: Warwick, RI
Screenshots
Here's some screenshots taht I have I just get a red line in the top section.chulett wrote:What's "not working" about it? People here would need details or your issue in order to provide any level of help.
How do I post screenshots into the msg board?
1st screenshot
2nd screenshot
jOE fOnSEca
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I haven't looked at the images, but a red line (link?) can mean simply that you've failed to join the link to the downstream stage - maybe pulled up 1mm short.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In a Lookup stage, in version 8, if you check the Range check box you must also double click and enter a range expression. This means that you are doing a range ("between") lookup. If you want just an ordinary lookup, uncheck the Range check box.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 17
- Joined: Mon Feb 18, 2008 7:56 pm
- Location: Warwick, RI
...
What I need is to do a Less Than or Equal to the Max(Hist_Date) and compair all the records in the fact table against this Max(Hist_Date)ray.wurlod wrote:In a Lookup stage, in version 8, if you check the Range check box you must also double click and enter a range expression. This means that you are doing a range ("between") lookup. If you want just an ordinary lookup, uncheck the Range check box.
jOE fOnSEca
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 17
- Joined: Mon Feb 18, 2008 7:56 pm
- Location: Warwick, RI
Example
I'm sorry i'm still new to Datastage. Can you send me some screenshots of what it would look like?ray.wurlod wrote:Then uncheck the Range check box, look up the max value, and perform the comparison in an expression in a Transformer stage or Filter stage.
That would be a giant help.
jOE fOnSEca