Hash Lookup to get the Exact Date

[New2DS]

I created a hash file implementing some logic and managed to get the data using a where clause in the input to get all the records where RR=0 into a hash file. If it gets two records with the same data it will merge into one with a delimeter

Code: Select all

ID Name	zip     Date
1  RR   1121	12/20/2001
2  CC	1122    05/04/2002
3  QQ   2233    06/04/2002|11/20/2002

In the hash file first 3 columns are the keys.Now using this hash file I have to do a lookup in which I have to populate

these dates into the column FDate.The derivation on the column should be

Code: Select all

IF RR=0 
THEN SDate
ELSE 
    If RR<>0 and 
        Input.ID=Hash.ID and Input.Name=Hash.Name and Input.zip=Hash.zip and SDate>=FDate
then Hash.FDate
( It should take either of the date for ID 3 based on the condition)

Code: Select all

For Example:

ID Name	zip     FDate		SDate		RR
1  RR   1121	 12/20/2001	 12/20/2001	0
1  RR   1121	 12/20/2001	 12/20/2001	0
1  RR   1121	 12/20/2001	 12/20/2001	1  
3  QQ   2233    06/04/2002   06/04/2002  0
4  QQ   2233    06/04/2002   07/06/2002  1
4  QQ   2233    06/04/2002   09/04/2002  1
4  QQ   2233    11/20/2002   11/20/2002  0 
4  QQ   2233    11/20/2002   12/04/2002  1
4  QQ   2233    11/20/2002   01/04/2003  1 

(If you look at the 8th record it should take the second record from the date column in the hashFile to satisfy the condition)


Hope I will be helped with some ideas on the derivation using field function or by some routines.

thanks

[sumitgulati]

What I would suggest is this:

1) While creating our hash file check the "Universe Stage Compatibility" option
2) In the main job make a look up to this hash file using a Universe Stage. This is because Universe Stage supports Relational operator in Lookup join conditions. Say the Universe stage name is HASHLKP.
3) Implement this IF condition
IF RR=0
THEN SDate
ELSE
If RR<>0
then HASHLKP.Date
END


This is going to be hit the performance because of the Lookup to Universe Stage.


Hope this helps
-Sumit