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
Range look up with UV stage
Moderators: chulett, rschirm, roy
Re: Range look up with UV stage
Sorry, see below...
Last edited by gateleys on Fri Oct 27, 2006 3:13 pm, edited 2 times in total.
Re: Range look up with UV stage
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
gateleys
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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
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
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.sumeet wrote: But I am confused , how do I join the dummy key( in hash file/UV stage) with source file.
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
So, your design looks like -
Code: Select all
NewHashedFile
\
SeqInput ----->Xfmr ------>SeqOut
![Cool 8)](./images/smilies/icon_cool.gif)
gateleys[/code]
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
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
![Confused :?](./images/smilies/icon_confused.gif)
thanks
SUmeet
Oops!!
Check these out:
viewtopic.php?t=94934&highlight=range+lookup
viewtopic.php?t=93532&highlight=range+lookup
![Embarassed :oops:](./images/smilies/icon_redface.gif)
Check these out:
viewtopic.php?t=94934&highlight=range+lookup
viewtopic.php?t=93532&highlight=range+lookup