Range look up with UV stage

[sumeet]

Hi ,

I had to do a range lookup ,so I followed what has been mentioned in other posts i.e. use UV stage and I ran into the following problem.

Source has column : Src_Date

Hash File--> UV stage has : date1 , date2, Yr_Month

My objective is: if Src_date is between date1 and date2 pull the Yr_month.

Now UV stage being Ref I wrote the SQL (in SQL for Reference Inputs) :

select date1 , date2, Yr_Month from "HashFile" where (date1>=? and date2 <=?)

Also in Transformer I linked the source and UV using Src_Date to both date1 and date2 .i.e. i made date1 and date2 as key.

But the output is not correct as I am getting single YR_MONTH for all the
src_date.

What should I do? DO i need to put the constraint with above condition in transformer?

Thanks
Sumeet

[gateleys]

Sorry, see below...

[gateleys]

Assuming the dates are in the format 'YYYY-MM-DD', I would just use the hashed file as reference, with a dummy key. In the transformer, the required column derivation would be

Code: Select all

If (Iconv(InLink.Src_Date,"D4-YMD") > Iconv(RefLink.date1,"D4-YMD") AND Iconv(InLink.Src_Date,"D4-YMD") < Iconv(RefLink.date2,"D4-YMD") Then RefLink.Yr_Month Else 'NO_MATCH_HANDLE_VALUE'

gateleys

[ray.wurlod]

What you should do is lie about which columns are the keys. Here you want DataStage to generate a WHERE clause containing your key columns. So tell it that Src_Date is a key column and that Copy_Of_Src_Date (organize this how you will) is also a key column.

[sumeet]

Hi ,

I went ahead with mentioned in previous post. I tried both the ways ..with UV stage and with regular hash file.

But the YR_MONTH which I am getting is same for all the records.

I know why this is happening. I have created a dummy key in hash file using @outrownum/KeyMgtGetNextValue().

But I am confused , how do I join the dummy key( in hash file/UV stage) with source file.

what I am doing:

src file ref getting expect

src_date cp_src_date |**date1 **date2 **yr_mon | yr_mon | yr_mon

19891027 19891027 19891001 19891031 198910 199505 198910
19900624 19900624 19900601 19900630 199006 199505 199006
19900925 19900625 19900601 19900630 199006 199505 199006
19950527 19950527 19950501 19950531 199505 199505 199505
20020126 20020126 20020101 20020131 200201 199505 200201

So I guess this wrong output is because of wrong dummy key join.

Please help me out.

Thanks
Sumeet

[gateleys]

But I am confused , how do I join the dummy key( in hash file/UV stage) with source file.

No, you do NOT have to perform a join. What you should be doing is, essentially, appending the columns of your hashed file to the source rows.

1. Pass your hashed file thru' a Xfmr and create a dummy field, say DummyID (Key), with type, say, Integer, length = 1. Pass other fields (date1 , date2, Yr_Month )as they are. In the derivation for DummyID, type 1.

Code: Select all

YourHashedFile ---> Xfmr ------> NewHashedFileWithDummyID

2. Use the NewHashedFileWithDummyID as your reference.
So, your design looks like -

Code: Select all

           NewHashedFile
                 \
SeqInput ----->Xfmr ------>SeqOut

In the Key expression property of the NewHashedFile, just type 1. There is no need to explicitly join the keys of the input and reference. Use the constraint that I had earlier posted, and you should be all smiles.

gateleys[/code]

[sumeet]

hi gateleys ,

There is a problem with this design.

I had done the same thing but if we dont make date1, date2 as keys then the hash file will give out just one record.

Infact when we are populating the hash file , only one record gets populated with just the DUMMY field as key.

This is a common thing with hash files.

Still no smile

thanks
SUmeet

[gateleys]

Oops!!
Check these out:
viewtopic.php?t=94934&highlight=range+lookup
viewtopic.php?t=93532&highlight=range+lookup