DSXchange

Hi All,

Need your suggestions on the following scenario. Considering we have the following input schema as

Input Schema:

StartDate
EndDate
Period1
Period2
Period3
.
.
.
Period30


Output Schema:
StartDate
EndDate
Period


Mapping:
StartDate -> StartDate
EndDate -> EndDate

(EndDate - StartDate) We get the no. of days and its only for one month. So it will be in between 1 to 30.

Based on no of days we get from the above subtraction we need to populate the respective Period Column to Output.

For ex: If the difference is 5 days (StartDate: 2011-06-06, EndDate: 2011-05-01: StartDate-EndDate=5) we need to populate Period5 from Source to Period column in Target.

This we can achieve with if then else in transformer but it will be tedious. Can anyone please help me achieving this in a better way?

Thank you,

A good logic. I think the simplest way to do it is in the source query itself if ur source is a database.
Use Decode, that will be more helpful than if else.

Thanks for your time. StartDate and EndDate are generating in middle of job using couple of lookups. Sadly, I cant write a query to accomplish it.

Try using Stage variables:

Variable1 = Period1:",":Period2:","Period3:",":Period4:",":...
Variable2 = Startdate-Enddate
Variable3 = field(Variable1,',',Variable2)

Then use Variable3 as the value to place in Period.

rschirm wrote:Try using Stage variables:

Variable1 = Period1:",":Period2:","Period3:",":Period4:",":...
Variable2 = Startdate-Enddate
Variable3 = field(Variable1,',',Variable2)

Then use Variable3 as the value to place in Period.

Brilliant, Thank you!!!

for this source is how many cols?

chandra.shekhar@tcs.com wrote:I think the simplest way to do it is in the source query itself if ur source is a database.

Ur was a city in ancient Babylonia. The second person personal possessive pronoun in English is spelled "your". Please maintain a professional standard of written English on DSXchange.