Page 1 of 1
Looping Date values in Transformers
Posted: Thu Oct 08, 2015 5:45 am
by skumar
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
Posted: Thu Oct 08, 2015 6:59 am
by chulett
What have you tried?
Posted: Thu Oct 08, 2015 7:22 am
by ShaneMuir
HINT:
DaysSinceFromDate AND DateFromDaysSince
Posted: Thu Oct 08, 2015 7:38 am
by chulett
Posted: Thu Oct 08, 2015 3:34 pm
by rkashyap
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.
Posted: Fri Oct 09, 2015 8:45 am
by qt_ky
Back to generating in between dates using loop variables, have you tried any function combined with the @ITERATION value?
Posted: Fri Oct 09, 2015 9:22 am
by chulett
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.
Posted: Sun Oct 11, 2015 9:30 pm
by skumar
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
Posted: Mon Oct 12, 2015 7:56 am
by rkashyap
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.
Posted: Mon Oct 12, 2015 3:13 pm
by chulett
Yeah, yeah, whatever.
