Help with a Hash-file

[dutchred]

Problem:
I have a flat file with 10 columns, one column is a date-field. This is input to a transform-stage. Then, I have a
hash-file with a single record, containing a date-field.
This is input to the same transform as a 'reference'. I
want to get all the records from the flat-file where it's
date-field is equal to or less than (<=) the date that is
in the hash-file. Can somebody help ?
I've tried using the 'constraints', but for some reason
nothing gets written to the output file, unless I tie up
the hash-file date with the flat-file date-field using the
"key-expression". The problem with this, it defaults to an
equi-join, and I only get output records whose dates are
equal to the hash-file date. I want all records whose date
is <= hash-file date. Thanks in advance.

 

[JGirl]

If the hash file only contains one record, why dont you create a batch that reads the record from the hash file, and loads the date field into a variable.
Then run the job you have created (without the hashfile lookup), passing the variable as a parameter to the job. Then in the transformer stage, create a conditional output that only write to the output file where the date is <= the parameter date?

J

 

[vmcburney]

The hash file stage only accepts exact matches to the key fields, making a < or between clause impossible. You can read the hash file using a Universe database stage instead as a hash file is a Universe table. The Universe stage does accept < clauses. Have Search the forum at

http://www.dsxchange.com

as this question has been covered a few times before.

 

[dutchred]

Thanks JGirl & vmcburney for the suggestions. I decided to
do some more tinkering and found another way to do it! I
still used the same transform with the flat-file input and
hash-file reference -> but, I "cheated". Since vmcburney
says that I'm limited to an equijoin, then I thought: "if
you can't beat 'em, join 'em...". I redid both the hash-
file & the flat-file by adding a "dummy" column to each,
where the dummy column contained the constant literals 'ZZ'.
Then I used this dummy column in the "key-expression" to
tie up both files in the equijoin I'm limited to. This time,
the transform took all the flat file records, and with my
constraints set up to filter for flat.date <= hash.date, it
worked perfectly! All flat-file records with their dates
greater than the hash-file records were rejected.