Joining 2 Datasets using between logic??
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 17
- Joined: Mon Nov 28, 2005 12:44 pm
- Contact:
Joining 2 Datasets using between logic??
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,
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,
Mark Stewart
Business Intelligence
Business Intelligence
Hi And welcome aboard
Moving to EE Forum.
Please post in the correct forum next time
(You posted this in the Sever jobs forum and your post concerns the Enterprise Edition forum)
Thanks in advance,
Moving to EE Forum.
Please post in the correct forum next time
(You posted this in the Sever jobs forum and your post concerns the Enterprise Edition forum)
Thanks in advance,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Re: Joining 2 Datasets using between logic??
[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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Joining 2 Datasets using 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.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.
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.
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.
Re: Joining 2 Datasets using between logic??
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
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
-
- Participant
- Posts: 17
- Joined: Mon Nov 28, 2005 12:44 pm
- Contact:
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
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.
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.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn