how i can achieve this result in datastage 7.5 px

[informdaya@gmail.com]

input lookup table

partno d_eff_date cost

1 10-Jan-12 10

1 10-Mar-12 12

1 10-Jun-12 15

1 15-Jul-12 20

input dataset

partno bulid date

1 10-Jan-12

1 11-Jan-12

1 11-Mar-12

output

partno bulid date d_eff_date cost shouldbe

1 10-Jan-12 10-Jan-12 10

1 11-Jan-12 10-Jan-12 10

1 11-Mar-12 10-Mar-12 12

[nani1974]

Hi,
I think you can do this by using Column_Export stage.

Thanks
Kumar.

[Kryt0n]

I think you can do this by using Column_Export stage.

I'm intrigued... how?

As for requirement, create a date range out of your lookup.

Or depending on data sizes and db abilities, do a sparse lookup getting rows with date <= current date (as per row) order by date desc and limit to 1

[nani1974]

Hi,
As per my understanding..part no is key and in the output there are four fields including the key...so now he wants all the data together in the output field as one(as per my understanding)...so for achieving that we can use column_export stage.

[Kryt0n]

Admittedly their request was as vague as can be managed but think they are trying to do a lookup, not merge columns

[informdaya@gmail.com]

how i can sparse lookup.?.its source is dataset.
yes part no is key. i have to pull out the cost in my target based on condition that the cost is applicable to that period.

[informdaya@gmail.com]

I think you can do this by using Column_Export stage.

How? i think u donot understand the question.

[informdaya@gmail.com]

Admittedly their request was as vague as can be managed but think they are trying to do a lookup, not merge columns

no sir..this is a real scenario in manufacturing domain.you have to pickup particular cost from lookup table..what was applicable in that period.

[ray.wurlod]

Upgrade to the current version and use a range lookup.

Otherwise do a sparse lookup as indicated, looking for the largest date smaller than your lookup date for that particular key value.

[bhasds]

Hi Informdaya,


I have tried the above scenario by taking two transformer, one after the source and another after the reference.In both the transformer stage I have used following steps-

1. Stage variables-

StgVar1=Seq(Field(InputColumn,"-",2,1)[1,1])

StgVar2=Seq(Field(InputColumn,"-",2,1)[2,1])

StgVar3=Seq(Field(InputColumn,"-",2,1)[3,1])

2. Added one extra column Lookup (Int).Derivation for which is as below-

StgVar1:StgVar2:StgVar3

3. Now in lookup stage lookup between the Lookup column of the Source and the reference.


OR

1.In transformer, in the derivation for the Lookup column-


MonthFromDate(StringToDate(InputColumn,"%yyyy-%mm-%dd")).

2.Now in lookup stage lookup between the Lookup column of the Source and the Reference.



In both the above cases I have done lookup operation on Month. It gave me the desired result.

Please correct me if understanding is not correct.

[informdaya@gmail.com]

Can you pls more explain on the process. for first table (ref table) look up key is (partno and d_effectv ). From main table it will be part_no and build_date should be equal to min of d_effectv. You told this is applicable for month only. Actully this ref table is duty rate table and might change that for few month or may be in a year. My main source hold 15 milion records and ref table conatin 10 min records.

[bhasds]

In that case, as Ray suggested it is possible through range lookup available in the latest version.