Joining 2 Datasets using between logic??

[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,

[roy]

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,

[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.

[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

[ray.wurlod]

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.

[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

[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.

[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.

[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.