Lookup help or Join

[joefonseca79]

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.

[ray.wurlod]

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).

[joefonseca79]

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?

[priyadarshikunal]

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?

once you get your premium account activated

[joefonseca79]

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?

once you get your premium account activated

it still doesn't seem to be working. Is there an issue with Lookup stage for v8?

[kcbland]

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.

[chulett]

What's "not working" about it? People here would need details or your issue in order to provide any level of help.

[joefonseca79]

What's "not working" about it? People here would need details or your issue in order to provide any level of help.

Here's some screenshots taht I have I just get a red line in the top section.

How do I post screenshots into the msg board?


1st screenshot


2nd screenshot

[kcbland]

Re-read Ray's post and make your job design match.

[ray.wurlod]

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.

[chulett]

Not a link nor a line. Joe, why not post the compiler error? It notes the problem and why your Key Expression is red - it doesn't like the 'Range' function you are using there... where did you get 'Range' from?

[ray.wurlod]

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.

[joefonseca79]

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.

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]

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.

[joefonseca79]

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.

I'm sorry i'm still new to Datastage. Can you send me some screenshots of what it would look like?

That would be a giant help.