Page 1 of 1
Joining 2 Datasets using between logic??
Posted: Thu Jan 12, 2006 1:49 pm
by MarkStewart
I've created the 1st data set where I've left the FIN_YEAR and FIN_MONTH columns blank. The EFFECTIVE_DATE has a date value on the same dataset.
I've created a 2nd data set with FIN_YEAR, FIN_MONTH, BILL_ST, BILL_END.
How do I join the two datasets? I need to get the FIN_YEAR and FIN_MONTH from the 2nd data set to poplulate those same columns on the 1st dataset where EFFECTIVE_DATE is between BILL_ST and BILL_END. Any suggestions? Basically how do you code a between clause between two data sets?
Thanks,
Posted: Thu Jan 12, 2006 4:33 pm
by roy
Hi And welcome aboard
![Smile :)](./images/smilies/icon_smile.gif)
Moving to EE Forum.
Please post in the correct forum next time
![Smile :)](./images/smilies/icon_smile.gif)
(You posted this in the Sever jobs forum and your post concerns the Enterprise Edition forum)
Thanks in advance,
Re: Joining 2 Datasets using between logic??
Posted: Fri Jan 13, 2006 2:18 am
by madhukar
[quote="MarkStewart"]I've created the 1st data set where I've left the FIN_YEAR and FIN_MONTH columns blank. The EFFECTIVE_DATE has a date value on the same dataset.
I've created a 2nd data set with FIN_YEAR, FIN_MONTH, BILL_ST, BILL_END.
How do I join the two datasets? I need to get the FIN_YEAR and FIN_MONTH from the 2nd data set to poplulate those same columns on the 1st dataset where EFFECTIVE_DATE is between BILL_ST and BILL_END. Any suggestions? Basically how do you code a between clause between two data sets?
Thanks,[/quote]
if u have any matching key between the dataset then u can use the lookup condition in look up stage to give the between logic.
Posted: Fri Jan 13, 2006 2:31 am
by balajisr
Hi
You need to use filter stage to filter the data based on effective data and join the two datasets based on the common key.
--Balaji S.R
Re: Joining 2 Datasets using between logic??
Posted: Fri Jan 13, 2006 2:58 am
by ray.wurlod
madhukar wrote:if u have any matching key between the dataset then u can use the lookup condition in look up stage to give the between logic.
Can you be more specific? I understand that the lookup condition property determines whether the lookup is performed at all; if this condition is satisfied then the key value is looked up. It's an "=" condition, "between" is not possible in a Lookup stage as far as I am aware.
You might have more luck with a Join stage. But - without having the means to check right now - I think even this has limits on what you can do with a WHERE condition.
Re: Joining 2 Datasets using between logic??
Posted: Fri Jan 13, 2006 5:05 am
by madhukar
ray,
2 options available in lookup
1 lookup on key
2 other than lookup key match , what else condition it has to meet
for the above req, we can give the following expn in lookup condition.
EFFECTIVE_DATE >= BILL_ST and EFFECTIVE_DATE <= BILL_END
Posted: Fri Jan 13, 2006 8:25 am
by MarkStewart
Thanks for the suggestions. Since there was no common key which is why I'd be doing a between clause I used a join with a dummey key. Then in the transformer stage wrote a contraint that contained the between condition. Worked like a charm.
Posted: Fri Jan 13, 2006 11:22 am
by ameyvaidya
Hi Mark,
I'd recommend a filter stage instead of the transformer.. Unless the transformer already exists as a necessary part of your job design. The filter should give you better performance.
Posted: Sun Jan 15, 2006 5:06 pm
by vmcburney
Call me crazy but isn't it easier and a whole lost faster to take BILL_DATE and derive the fields FIN_YEAR and FIN_MONTH instead of looking them up? It should be a relatively easy formula.
If that is not viable then why not map every FIN_YEAR and FIN_MONTH combination to a calendar year and calendar month. You then turn BILL_DATE into BILL_YEAR and BILL_MONTH and join them to the financial year lookup via key fields to CALENDAR_YEAR and CALENDAR_MONTH fields and bring back the FIN values.
I would avoid between lookups unless you have low data volumes and you can afford to load and filter all the extra rows.