Looping Date values in Transformers

skumar · Post by **skumar** » Thu Oct 08, 2015 5:45 am

Hi All,

I have a single record from source which contains minimum date - 2015/10/01 and maximum date - 2015/10/31.
Now, In target I want to generate in between dates using loop variables. So my target should contain 31 records including the min and max dates.

can some one help me on this.

Thanks
skumar

chulett · Post by **chulett** » Thu Oct 08, 2015 6:59 am

What have you tried?

ShaneMuir · Post by **ShaneMuir** » Thu Oct 08, 2015 7:22 am

HINT:
DaysSinceFromDate AND DateFromDaysSince

chulett · Post by **chulett** » Thu Oct 08, 2015 7:38 am

rkashyap · Post by **rkashyap** » Thu Oct 08, 2015 3:34 pm

You may leverage Oracle hierarchical functions thru Sparse Lookup for this.

Code: Select all

SELECT (to_date(:STARTDATE,'YYYYMMDD') + (level - 1)) DateListVal FROM dual
CONNECT BY level <= (to_date(:ENDDATE,'YYYYMMDD') - to_date(:STARTDATE,'YYYYMMDD') + 1)

STARTDATE and ENDDATE: Input date values in YYYYMMDD / VarChar format.
DateListVal: Output date list / Date format.

qt_ky · Post by **qt_ky** » Fri Oct 09, 2015 8:45 am

Back to generating in between dates using loop variables, have you tried any function combined with the @ITERATION value?

chulett · Post by **chulett** » Fri Oct 09, 2015 9:22 am

Yah, not really sure why Oracle got thrown into the mix. It doesn't answer the question that was asked and no database was mentioned, let alone a specific one.

skumar · Post by **skumar** » Sun Oct 11, 2015 9:30 pm

I tried using the loop variables with @ITERATION function. it worked well for me. here is the logic.

Stage variable1 - Min Date
Stage Variable2 -Max Date
Stage Variable3 - JulianDay(MaxDate)-JulianDay(MinDate)

Now, In loop constraint @ITERATION <=StageVariable3
In derivation of LoopVar - DateoffsetbyDays(Stagevariable1,@ITERATION-1)

I got the answer hence i am marking it as resolved. thanks all for your support

Thanks
skumar

rkashyap · Post by **rkashyap** » Mon Oct 12, 2015 7:56 am

Skumar ... Cool.

And to answer previous questions ... Inspired by creative solutions and thinking outside of the box, that I have seen in this forum, like suggestions to leverage server job's capabilities when the OPs question was in parallel forum or advising them to use an Oracle literal when title mentioned bind variables

Unless it is an interview question, developers are more interested in solving the issue, than strictly using a particular stage. In this case, preexisting code for similar req (date dim creation) was shared with OP.

chulett · Post by **chulett** » Mon Oct 12, 2015 3:13 pm

Yeah, yeah, whatever.